How to add a Default constraint while creating a table? SQL Server [closed]
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
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 add 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:
< table> ::= [ 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.