How SQLServer add a default value for a column?
I am using MS SQL Server. I have an "action" table with an "action_type" column that I want to set a default value of "to do" for. My DDL in MS SQL Studio errors! I have read many many articles trying to work out what I am doing wrong but nothing seems to work. The DDL is as follows, the table CREATE works, and the ALTER fails.
CREATE TABLE "action" ( "action" INT IDENTITY(1,1) , "name" VARCHAR(250) NOT NULL , "owner" VARCHAR(50) , "action_type" VARCHAR(50) NOT NULL , "due_date" DATETIME , "done_date" DATETIME , "successful" BIT NOT NULL , "job" INT , "contract" INT , "person" INT , "description" VARCHAR(MAX) , "deliverable" VARCHAR(MAX) , "outcome" VARCHAR(MAX) , "source" VARCHAR(50) , "notes" VARCHAR(MAX) ); ALTER TABLE "action" ADD CONSTRAINT "df_action_0" DEFAULT "To Do" FOR "action_type";The ALTER statement returns error
Msg 128, Level 15, State 1, Line 4 The name "To Do" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Based on various articles I have tried replacing "To Do" with 'To Do', N'To Do', ("To Do") and others, nothing works. How does sql server add column with default?
SQL Server add column with default using:
TER TABLE table_name ADD column_name tada_type NOT NULL CONSTRAINT constraint_name DEFAULT default_value; ALTER TABLE table_name ADD column_name data_type NULL CONSTRAINT constraint_name DEFAULT default_value WITH VALUES;
SQL Servers DEFAULT is SET QUOTED_IDENTIFIER ON and with this literals must be delimited by single quotation marks. So, you really only need to change this part: DEMO
ALTER TABLE "action" ADD CONSTRAINT "df_action_0" DEFAULT 'To Do' FOR "action_type";Though, you will more commonly see double quotes not used in this manner, and instead your DDL would be written as:
CREATE TABLE action ( action INT IDENTITY(1,1) , name VARCHAR(250) NOT NULL , owner VARCHAR(50) , action_type VARCHAR(50) NOT NULL , due_date DATETIME , done_date DATETIME , successful BIT NOT NULL , job INT , contract INT , person INT , description VARCHAR(MAX) , deliverable VARCHAR(MAX) , outcome VARCHAR(MAX) , source VARCHAR(50) , notes VARCHAR(MAX) ); ALTER TABLE action ADD CONSTRAINT df_action_0 DEFAULT 'To Do' FOR action_type;