修改SQLServer的默认约束,如果存在首先删除再添加  

今天有人问,写写看了,顺便自己备个份

 
IF EXISTS(SELECT * FROM SysObjects WHERE Name='CloveTest')
 DROP TABLE CloveTest
GO
CREATE TABLE CloveTest
(CNO INT PRIMARY KEY IDENTITY(1,1),
 Cname VARCHAR(20),
 TestValue VARCHAR(20))
GO
--Insert Test
INSERT INTO CloveTest(TestValue) VALUES('One')
GO
--Show Test Value
SELECT * FROM CloveTest
GO
--ADD Constaint 
ALTER TABLE CloveTest ADD DEFAULT 'Clove' FOR Cname
GO
--Insert Test
INSERT INTO CloveTest(TestValue) VALUES('Two')
GO
--Show Test Value
SELECT * FROM CloveTest
GO
--Drop Any Constraint On Column Cname
IF EXISTS(select name from sysobjects where xtype='d' AND ID IN (
 SELECT Constid FROM SysConstraints WHERE ID=Object_id('CloveTest') AND Colid IN (
 SELECT Colid FROM SysColumns WHERE ID=Object_id('CloveTest') AND name='Cname')))
BEGIN
 DECLARE @ConstraintName VARCHAR(100)
 DECLARE @RunSql VARCHAR(200)
 SET @ConstraintName=(select name from sysobjects where xtype='d' AND ID IN (
 SELECT Constid FROM SysConstraints WHERE ID=Object_id('CloveTest') AND Colid IN (
 SELECT Colid FROM SysColumns WHERE ID=Object_id('CloveTest') AND name='Cname')))
 SET @RunSql = 'ALTER TABLE CloveTest DROP CONSTRAINT '+@ConstraintName
 EXECUTE(@RunSql)
END
GO
--Insert Test
INSERT INTO CloveTest(TestValue) VALUES('THREE')
GO
--Show Test Value
SELECT * FROM CloveTest

分享到：