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:
Looking at the object explorer in SSMS shows the name:
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