Declaring a default constraint when creating a table

TsqlSql Server-2000DefaultConstraints

Tsql Problem Overview


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.

Tsql Solutions


Solution 1 - Tsql

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAlbireoView Question on Stackoverflow
Solution 1 - TsqlDavid MView Answer on Stackoverflow