Declaring a default constraint when creating a table

Question

I am creating a new table in Microsoft SQL server 2000 by writing the code instead of using the GUI, I am trying to learn how to do it "the manual way".

This is the code I am actually using, and it works fine:

CREATE TABLE "attachments"
(
    "attachment_id" INT NOT NULL,
    "load_date" SMALLDATETIME NOT NULL,
    "user" VARCHAR(25) NOT NULL,
    "file_name" VARCHAR(50) NOT NULL,
    CONSTRAINT "pk_attachments" PRIMARY KEY ("attachment_id"),
    CONSTRAINT "fk_users" FOREIGN KEY ("user") REFERENCES "users" ("user"),
    CONSTRAINT "ch_load_date" CHECK ("load_date" < GETDATE())
)

I have specified the primary key, foreign key and check constraints on their own because in this way I can define a name for them, otherwise declaring them inline would make SQL Server generate a random name, and I do not "like" it.

The problem arose when I tried to declare the default value constraint: looking at the informations on the internet and how Microsoft SLQ Server Management Studio creates it, I understood that it can be created both inline and on its own:

"load_date" SMALLDATETIME NOT NULL DEFAULT GETDATE()

or

CONSTRAINT "df_load_date" DEFAULT GETDATE() FOR "load_date"

The inline method works fine, but it generates as usual a random name for the constaint, the stand alone method throws an error, saying Incorrect syntax near 'FOR'..

Also, if I create the table and then ALTER it, the command works:

ALTER TABLE "attachments"
ADD CONSTRAINT "df_load_date" DEFAULT GETDATE() FOR "load_date"

As a reference, here is the full code I am trying to execute:

CREATE TABLE "attachments"
(
    "attachment_id" INT NOT NULL,
    "load_date" SMALLDATETIME NOT NULL,
    "user" VARCHAR(25) NOT NULL,
    "file_name" VARCHAR(50) NOT NULL,
    CONSTRAINT "pk_attachments" PRIMARY KEY ("attachment_id"),
    CONSTRAINT "fk_users" FOREIGN KEY ("user") REFERENCES "users" ("user"),
    CONSTRAINT "ch_load_date" CHECK ("load_date" < GETDATE()),
    CONSTRAINT "df_load_date" DEFAULT GETDATE() FOR "load_date"
)

I'm totally at loss here, is what I am trying to do not possible, or I am doing something wrong?

Edit:

David M showed how to add a named default constraint using the inline syntax, I am still looking to understand if the stand alone syntax is completely wrong or it is my fault.

I agree with the edit. David M's response does not cover how to add a constraint via a stand-alone constraint declaration, but since BOL doesn't have any examples where you can name the default constraint *except* via the way David M demonstrated, I think it's safe to assume SQL Server (inconsistently) doesn't support this syntax. — Peter Majeed, Sep 30 '11 at 17:06
See also: [Incorrect syntax near 'for' SQL Server](https://stackoverflow.com/questions/42440431/) — paulsm4, Aug 16 '17 at 04:00

David M · Accepted Answer · 2015-08-16T13:10:28.880

179

Do it inline with the column creation:

[load_date] SMALLDATETIME NOT NULL
        CONSTRAINT [df_load_date] DEFAULT GETDATE()

I have used square brackets rather than quotes as many readers won't work with QUOTED_IDENTIFIERS on by default.

edited Aug 16 '15 at 13:10

answered Nov 23 '09 at 12:01

David M

68,374
13
151
181

3

Thanks, that resolves the name problem. Now I am trying to figure out if this behaviour is "by design" (i.e. it is not possible to do it) or if there is a way to do it. You know, I like to keep my code "tidy" and having the constraints declared after the columns makes the SQL files clearer and easier to understand and debug (or at least it is what I think). – Albireo Nov 23 '09 at 12:25
3

@Albireo - By design. `table_constraint` in [the grammar](http://technet.microsoft.com/en-us/library/ms174979.aspx) doesn't include `DEFAULT` – Martin Smith Oct 14 '13 at 15:28
Can I set up SSMS to generate the table script this way ? – yucer May 23 '14 at 08:08
2

This solution only works for me when I remove the quotes around the field and constraint names. – David S. Jan 02 '15 at 15:12
1

For newer versions of SQL Server use `[load_date] SMALLDATETIME NOT NULL CONSTRAINT [df_load_date] DEFAULT GETDATE()`. Notice the square brackets instead of double quotes. – deadlydog Aug 05 '15 at 16:51
3

Not actually a newer/older version thing - `SET QUOTED_IDENTIFIER` toggles. I will revise the answer, as I prefer the square brackets anyway, I had just followed the style of the OP's question. – David M Aug 16 '15 at 13:09

Declaring a default constraint when creating a table

1 Answers1

Linked