DEFAULT Constraint syntax

Question

Post reply

DEFAULT Constraint syntax

c00ler01

Ten Centuries

Points: 1180
More actions
January 6, 2010 at 12:44 pm

#208706

Hi guys,
I'm writing few new tables and I'd like to keep all the constraints named according to my standards.
I have no problems with indexes, checks etc...but with DEFAULT
In my table definition I have
Create Table User
(
...
ActiveFromDate DateTime Not Null,
...
Constraint [DF_CMS_User_ActiveFromDate] Default (GetDate()) For ActiveFromDate,
...
)
Message I get is Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'for'.
I have scripted the constraint from previous table and I've got the following:
ALTER TABLE [dbo].[CMS_User] ADD DEFAULT (getdate()) FOR [ActiveFromDate]
GO
Thank you 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Create Table User (

...

ActiveFromDate DateTime Not Null Constraint [DF_CMS_User_ActiveFromDate] Default (GetDate()),

... other columns here ...

)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

c00ler01 Ten Centuries Points: 1180 More actions · Answer 2

Hi,

Thank you for your reply, but I'm looking for a seperate constraint which I can place after my column definitions.

E.g.

Col1 Integer,

Col2 VarChar(20)

-- PK: Primary key constraints

-- CK: Check constraints

-- DF: Constraints

This is where I'd like to have my constraint

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Can't be done. In the create table statement, the default must be attached to the column. The only constraints allowed to go at the end, after the columns are Primary Key, Unique, Check and Foreign key.

You can check the syntax guide in Books online, it's clear about this. This is the valid options for the table constraint (constraints defined after the columns)

[ CONSTRAINT constraint_name ]

{

{ PRIMARY KEY | UNIQUE }

[ CLUSTERED | NONCLUSTERED ]

(column [ ASC | DESC ] [ ,...n ] )

[

WITH FILLFACTOR = fillfactor

|WITH ( <index_option> [ , ...n ] )

]

[ ON { partition_scheme_name (partition_column_name)

| filegroup | "default" } ]

| FOREIGN KEY

( column [ ,...n ] )

REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

[ NOT FOR REPLICATION ]

| CHECK [ NOT FOR REPLICATION ] ( logical_expression )

}

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

From BOL:

Adding a constraint to an existing column:

ALTER TABLE dbo.doc_exz

ADD CONSTRAINT col_b_def

DEFAULT 50 FOR column_b ;

Lowell SSC Guru Points: 323504 More actions · Answer 5

both of these syntaxes worked fine for me, against know tables for me; i don't get the syntax error you were using

ALTER TABLE [dbo].[MYTBL1] ADD DEFAULT (getdate()) FOR [APPLDT]

ALTER TABLE [dbo].[MYTBL1] ADD CONSTRAINT [DF_MYCONSTRAINT] DEFAULT (getdate()) FOR [APPLDT2]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

c00ler01 Ten Centuries Points: 1180 More actions · Answer 6

Thank you GilaMonster, this makes perfect sense!

Alter table statement has worked from the start, I was after the statement that I could include after the column definitions.

Thank you very much, the problem has been solved.

Neeraj Prasad Sharma Ten Centuries Points: 1295 More actions · Answer 7

Create Table TableWithdefaultvalue (ID Int Constraint DF_ID DEFAULT(0) , name Varchar(10) , Country Varchar(10) )

Here we have ID column which will accept value 0(zero) as default value in table.

Here default column in numeric datatatype , however we can make default column with any datatype.

For existing table

Create Table TableWithdefaultvalue2 (ID Int Constraint DF_ID DEFAULT(0) , name Varchar(10) , Country Varchar(10) )

alter table TableWithdefaultvalueVarchar add CONSTRAINT DEF_ID default(1) forID

REF

Neeraj Prasad Sharma
Sql Server Tutorials

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Please note: 3 year old thread

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass