Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

put on hold as off-topic by Max Vernon, Tom V, jcolebrand 13 hours ago

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Tom V, jcolebrand
If this question can be reworded to fit the rules in the help center, please edit the question.

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.

share|improve this answer
    
I want to name my constraint, which wouldn't be possible if I do it inline. Is there a way without using the inline or alter table statement. – Dhruv Raj 15 hours ago
4  
@DhruvRaj - it is possible as this answer shows – Martin Smith 15 hours ago
2  
The code sample at the top of this answer works perfectly when you remove the 3 '..' – Kevin3NF 14 hours ago
1  
@Kevin3NF yes, thnx. Now it works without removing anything ;) – TypoCubeᵀᴹ 14 hours ago

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
share|improve this answer
    
I want to name my constraint, which wouldn't be possible if I do it inline. Is there a way without using the inline or alter table statement. – Dhruv Raj 15 hours ago

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_TestSomeData:

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

share|improve this answer

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