Declaring a default constraint when creating a table

Question

Hi, 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?

Thanks in advance, Andrea.

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.

Answer 1

Do it inline with the column creation:

"load_date" SMALLDATETIME NOT NULL
        CONSTRAINT "df_load_date" DEFAULT GETDATE()