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

499    Asked by Dannasahi in SQL Server , Asked on Apr 22, 2021

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'' What are SQL Server constraints?

Answered by Anna Ball

Constraints in SQL Server are predefined rules and restrictions that are enforced in a single column or multiple columns, regarding the values allowed in the columns, to maintain the integrity, accuracy, and reliability of that column's data. To resolve this error 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:

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

Your Answer