3

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.

closed as off-topic by LowlyDBA, kevinsky, Andriy M, mustaccio, Joe Obbish Jun 26 at 23:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, kevinsky, Andriy M, mustaccio, Joe Obbish
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Just from a design perspective many of your Varchar fields should be replaced by Int values that are a foreign key to another table. You need a table of Owners, Action Types, Outcomes. Then your default value is an Int which will work. – kevinsky Jun 26 at 12:28
11

It's your quotes. If you remove those, and change your literal to use single quotes, you should be fine. 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;

Not the answer you're looking for? Browse other questions tagged or ask your own question.