Need help with default constraint...

WebAppDeveloper
WebAppDeveloper used Ask the Experts™
on
Hi,

I have the following script that has already been run in certain databases. For some reason, my dba did not run this script in the production environment, and instead, he added the  SequenceNumber column manually. I want to write a script (to be run in production) to check if the SequenceNumber (that was manually added by the dba) has a default constraint with default of 0. If it does not, then add "CONSTRAINT DF_SeqNumber DEFAULT 0 WITH VALUES".  In other words, I want to make sure that whenever a new record goes into the 'tblProducts' table in production, a default value of 0 is inserted into the SequenceNumber column.  If you can provide SQL syntax for such script, that will be great.

Thanks.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'tblProducts' AND COLUMN_NAME = 'SequenceNumber')
BEGIN
   ALTER TABLE dbo.tblProducts ADD SequenceNumber INT NULL CONSTRAINT DF_SeqNumber DEFAULT 0 WITH VALUES
END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Narender GakkaAWS / DevOps / Cloud Consultant

Commented:
Just run this script in the server it will suffice

Author

Commented:
But the column SequenceNumber already exists in the table though. This script says "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblProducts' AND COLUMN_NAME = 'SequenceNumber')
...."

Thanks.
Database Consultant
Top Expert 2009
Commented:
Hello WebAppDeveloper,

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblProducts' AND COLUMN_NAME = 'SequenceNumber' AND column_default is null)
BEGIN
   ALTER TABLE dbo.tblProducts ADD SequenceNumber INT NULL CONSTRAINT DF_SeqNumber DEFAULT 0 WITH VALUES
END


Regards,

Aneesh

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial