DEFAULT CONSTRAINT full form

Question

For each table constraint, I see that there are shorthand form, semi-shorthand form and full form (Pardon me for the naming. Suggestions of how to correctly call them are welcome)

For example

Shorthand form: let constraint name be defined by SQL Server, column to be affected is implied as the column being defined

CREATE TABLE myTable
(
    id int PRIMARY KEY,
)

Semi-shorthand form: explicit constraint name, implied column

id int CONSTRAINT PK_ID PRIMARY KEY,

Full form : both explicit. Allow definition at the end separated from column definition

id int,
Name varchar(40),
CONSTRAINT PK_ID PRIMARY KEY(id)

From what I see, the full form is seen often in ALTER TABLE, where constraints are defined afterwards.

For DEFAULT constraint

Short-hand form

id int DEFAULT newid()

Semi-shorthand form

id int CONSTRAINT DF_ID DEFAULT newid()

I see a full-form use in ALTER TABLE as here

ALTER TABLE [dbo].[Customers] WITH NOCHECK ADD 
  CONSTRAINT [DF__Customers__cust___151B244E] 
  DEFAULT ('new customer') FOR [cust_name],
  CONSTRAINT [def_last_updated_by] DEFAULT 
  (suser_sname()) FOR [last_updated_by],
  PRIMARY KEY  CLUSTERED 
  ([cust_id])  ON [PRIMARY] 
GO

However, full-form definition when creating table fails

CREATE TABLE testGUIDs
(
    ID uniqueidentifier,
    Name varchar(40),
    CONSTRAINT DF_ID DEFAULT newid() FOR [ID]
)
GO

with an error

Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'for'.

Can anyone suggest why this error occurs and how to use full-form definition of Default constraint?

Answer 1

The error occurs because the syntax of a default constraint in a CREATE TABLE command is different to that in ALTER TABLE. Just because a command uses one syntax you cannot assuming it is the same for all commands.

In a CREATE TABLE command the syntax requires a constraint with a default to be defined after the column:

<column_definition> ::=column_name <data_type>
...
[ CONSTRAINT constraint_name ] DEFAULT constant_expression ] 
...

With the constant_expression defined as:

Is a constant, NULL, or a system function that is used as the default value for the column.

But in the ALTER TABLE the syntax is:

...
| ADD <table_constraint> 
...

The syntax of the table_contraint is:

[ CONSTRAINT constraint_name ] 
{ 
    ...
    | DEFAULT constant_expression FOR column [ WITH VALUES ] 
    ...
}

A number of changes can be defined in a single ALTER TABLE command, so the column name needs to be specified.

So there is no "full form" syntax for CREATE TABLE.

Answer 2

You need to declare it next to the column

CREATE TABLE testGUIDs
(
    ID uniqueidentifier CONSTRAINT DF_ID DEFAULT newid(),
    Name varchar(40)    
)
GO

A default constraint can only apply to a single column.

The grammar for table_constraint doesn't include Default which would allow it to be declared at the end.

Answer 3

1. id int PRIMARY KEY can only be applied to a single-field PK. The constraint is given a default name by the system. This is a problem if you need the name later in order to manipulate the constraint and you don't know the name.
2. id int CONSTRAINT PK_ID PRIMARY KEY can only be applied to a single-field PK.
3. CONSTRAINT PK_ID PRIMARY KEY(col1, col2) can be applied to compound primary keys.

As for your problem: Why not use the inline variant? A default constraint can only be applied to a single column anyway. Your syntax is simply not supported.