更新时间:2023-01-20 11:38:18
如果在这里简单地禁用约束是一个选项,您可以使用:
If simply disabling constraints is an option here, you can use:
ALTER TABLE myTable NOCHECK CONSTRAINT all
然后你可以简单地使用:
then you can switch them back on simply using:
ALTER TABLE myTable WITH CHECK CHECK CONSTRAINT all
如果你想在所有表中禁用约束,你可以使用:
If you want to disable constrains in all tables you can use:
-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-- enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
更多问题:外键约束是否可以暂时禁用使用 TSQL?
但如果您需要永久删除约束,您可以使用 此脚本发布在 databasejurnal.com 上.
But if you need to drop constraints permanently you can use this script posted on databasejurnal.com.
稍微修改一下,只去掉外键
Just modify it slightly to only drop the foreign keys
create proc sp_drop_fk_constraints
@tablename sysname
as
-- credit to: douglas bass
set nocount on
declare @constname sysname,
@cmd varchar(1024)
declare curs_constraints cursor for
select name
from sysobjects
where xtype in ('F')
and (status & 64) = 0
and parent_obj = object_id(@tablename)
open curs_constraints
fetch next from curs_constraints into @constname
while (@@fetch_status = 0)
begin
select @cmd = 'ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @constname
exec(@cmd)
fetch next from curs_constraints into @constname
end
close curs_constraints
deallocate curs_constraints
return 0