禁用并重新启用一个约束
下例禁用用于限制可接受的薪水数据的约束。WITH NOCHECK CONSTRAINT 与 ALTER TABLE 一起使用,以禁用该约束并使正常情况下会引起约束违规的插入操作得以执行。WITH CHECK CONSTRAINT 重新启用该约束。CREATE TABLE cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
)-- Valid inserts
INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)-- Reenable the constraint and try another insert, will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,"Eric James",110000)
下例禁用用于限制可接受的薪水数据的约束。WITH NOCHECK CONSTRAINT 与 ALTER TABLE 一起使用,以禁用该约束并使正常情况下会引起约束违规的插入操作得以执行。WITH CHECK CONSTRAINT 重新启用该约束。CREATE TABLE cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
)-- Valid inserts
INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)-- Reenable the constraint and try another insert, will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,"Eric James",110000)
我这有百十张表呢,每次需要操作的表也会不同,不可能直接去写固定的值的。希望大家帮忙在系统表中想想办法。最近项目实在太忙,没时间去找系统表中的关系了。拜托大家了,分可以加的
ALTER TABLE test drop CONSTRAINT PK_testALTER TABLE test add CONSTRAINT PK_test primary key (ID)问题是,你删除主键或唯一性约束后如果插入了重复值,那么后面重新添加主键或约束就不能成功执行的
ALTER TABLE test drop CONSTRAINT PK_testALTER TABLE test add CONSTRAINT PK_test primary key (ID)问题时,你删掉主键或者唯一性约束后,插入了重复值得话,后面重新加主键或者唯一性约束就不能成功执行
@tbname sysname, --要取主键列名的表名
@re nvarchar(1000) out --取得的主键列名
as
declare @Obj_ID int
select @Obj_ID=object_id(@tbname),@re=''
select @re=@re+','+name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
set @re=stuff(@re,1,1,'')
go--调用
declare @re nvarchar(1000)
exec p_GetPrimaryKey 'jobs',@re out
select jobs表的主键列名=@re
不过我还没仔细看,等我仔细看过后就结贴分不够的话尽管说,我7000多分呢:)