How Do I Specify a Column Name for a Default Constraint?

Question

I know I can specify it inline like this:

CREATE TABLE dbo.MyTable
(
    Id INT NOT NULL CONSTRAINT PK_MyTable_Id PRIMARY KEY IDENTITY,
    Created DATETIME NOT NULL CONSTRAINT DF_MyTable_Created DEFAULT GETDATE()
)

But I'd like to put all my constraints at the end of the table definition to keep them separate. I can't seem to find out how to name the column for the default constraint if I put it at the end of the table definition. I can do it with primary/foreign keys and unique constraints, but don't know how to specify the column name with a default constraint.

CREATE TABLE dbo.MyTable
(
    Id INT NOT NULL IDENTITY,
    Created DATETIME NOT NULL,
    CONSTRAINT PK_MyTable_Id PRIMARY KEY( Id ),
    CONSTRAINT DF_MyTable_Created DEFAULT GETDATE()
)

SQLMenace · Accepted Answer · 2009-04-22 19:49:47Z

2

Do it separate with ALTER TABLE ADD..... syntax

like this

CREATE TABLE dbo.MyTable
(
    Id INT NOT NULL IDENTITY,
    Created DATETIME NOT NULL

)
GO

ALTER TABLE dbo.MyTable ADD CONSTRAINT PK_MyTable_Id PRIMARY KEY( Id ),
CONSTRAINT DF_MyTable_Created DEFAULT GETDATE() FOR Created
GO

answered Apr 22, 2009 at 19:49

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Josh Close Over a year ago

I'd rather do it inline than altering the table.

SQLMenace Over a year ago

in that case you would have to do your first option

Josh Close Over a year ago

I take it it's just not possible for a default constraint?

Collectives™ on Stack Overflow

How Do I Specify a Column Name for a Default Constraint?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related