Reg:Checking default contraint exist

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Reg:Checking default contraint exist

Author

Topic

ycr1988
Starting Member

3 Posts

Posted - 2013-06-28 : 05:09:55

Hi All,

I have below query to check that default constraint exist if not adding default constraint but this gives me error.

if not(exists(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'orderhdr' AND COLUMN_NAME = 'stemail' AND COLUMN_DEFAULT IS NOT NULL))
begin
ALTER TABLE [dbo].[orderhdr] ADD DEFAULT ((' ')) FOR stemail
End

Thanks,
Chakrahdar.

Ycr

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 05:27:40

should be this


if NOT EXISTS(SELECT 1 FROM sys.default_constraints WHERE OBJECT_NAME(object_id) = 'orderhdr' AND COL_NAME(object_id,parent_column_id)= 'stemail') 
begin
ALTER TABLE [dbo].[orderhdr] ADD CONSTRAINT DF_orderhdr_stemail DEFAULT ' ' FOR stemail
End

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ycr1988
Starting Member

3 Posts

Posted - 2013-06-28 : 05:48:09

Hi,

I have tried your code it gives me error "Column 'stemail' in table 'orderhdr' is invalid for creating a default constraint."

Thanks,
Chakradhar.

Ycr

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 05:54:32

whats the datatype and nullability of column orderhdr?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources