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

Posted on

Question :

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”

Answer :

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 <table_constraint> are PRIMARY KEY, FOREIGN KEY and CHECK constraints:

< 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 )   
}

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.

Your comments on the other two answers claim that you cannot name a default constraint when creating it “inline”. Both answers show that you can, in fact, provide a name for the constraint when creating it inline. I’ll add a third example, showing the results.

IF OBJECT_ID('dbo.Test') IS NOT NULL
DROP TABLE dbo.Test;
CREATE TABLE dbo.Test
(
    TestID int NOT NULL
        CONSTRAINT PK_Test --here I'm naming the primary key constraint!
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData varchar(42) NOT NULL
        CONSTRAINT DF_Test_SomeData --this is the name of the default constraint!
        DEFAULT ('Carrie Fisher')
);

INSERT INTO dbo.Test DEFAULT VALUES;

This shows the name of the default constraint is DF_Test_SomeData:

SELECT TableName = t.name
    , ConstraintName = dc.name
FROM sys.default_constraints dc
    INNER JOIN sys.tables t ON dc.parent_object_id = t.object_id;

Results:

enter image description here

Looking at the object explorer in SSMS shows the name:

enter image description here

You can use default value on the field definition.

Create tblTest(
columns..
..
..
Gender int CONSTRAINT constraint_name DEFAULT 3,
..
..
..
)

Or use ALTER TABLE:

ALTER TABLE tblTest
ADD CONSTRAINT constraint_name 
DEFAULT 3
FOR Gender

Leave a Reply

Your email address will not be published. Required fields are marked *