snippetsqlMajor

How to add a Default constraint while creating a table? SQL Server

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

whilehowcreatingsqldefaultconstraintservertableadd

Problem

I am trying to create a new table with columns followed by their constraint as shown below.

Create tblTest(
columns..
..
..
Gender int,
Constraint DF_tblTest_Gender Default 3 For Gender,
..
..
..
)

However, I am getting an error message near the default constraint as,

'Incorrect syntax near 'for''

Solution

You can name the constraint inline:

CREATE TABLE tblTest(
  --
  --
  Gender int CONSTRAINT DF_tblTest_Gender DEFAULT 3,
  --
) ;

As the CREATE TABLE msdn page shows:

DEFAULT

... To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT.

In the same page, we can find that the only options for ` are PRIMARY KEY, FOREIGN KEY and CHECK

 constraints:

 ::=  
  [ CONSTRAINT constraint_name ]   
{  
   { PRIMARY KEY | UNIQUE }  
       {   
         NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])  
         | NONCLUSTERED HASH (column [ ,... n ] ) 
               WITH ( BUCKET_COUNT = bucket_count )   
       }   
    | FOREIGN KEY   
        ( column [ ,...n ] )   
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]   
    | CHECK ( logical_expression )   
}


so if you want to add a default constraint (naming or not) the only ways are by doing it inline or with an

ALTER TABLE` statement.

Code Snippets

CREATE TABLE tblTest(
  --
  --
  Gender int CONSTRAINT DF_tblTest_Gender DEFAULT 3,
  --
) ;

< table_constraint > ::=  
  [ CONSTRAINT constraint_name ]   
{  
   { PRIMARY KEY | UNIQUE }  
       {   
         NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])  
         | NONCLUSTERED HASH (column [ ,... n ] ) 
               WITH ( BUCKET_COUNT = bucket_count )   
       }   
    | FOREIGN KEY   
        ( column [ ,...n ] )   
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]   
    | CHECK ( logical_expression )   
}

Context

StackExchange Database Administrators Q#159357, answer score: 35

Revisions (0)

No revisions yet.