16

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''

1
  • You might want to have a look at the official Microsoft SQL Server documentation for the CREATE TABLE ... statement. You have a very basic syntax error.
    – John K. N.
    Mar 5, 2020 at 15:51

3 Answers 3

29

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.

0
11

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

4

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.