如何在 Sql Server 2000 中删除表上的所有外键约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1438654/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I drop all foreign-key constraints on a table in Sql Server 2000?
提问by Mathias
How do I drop all foreign-key constraints on a table in SQL Server 2000 using T-SQL?
如何使用 T-SQL 在 SQL Server 2000 中删除表上的所有外键约束？
回答by kristof
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"
More in the question: Can foreign key constraints be temporarily disabled using TSQL?
更多问题：能否使用 TSQL 暂时禁用外键约束？
But if you need to drop constraints permanently you can use this script posted on databasejurnal.com.
但是，如果您需要永久删除约束，则可以使用发布在 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
回答by leppie
Here you go: (not tested on SQL2000, but should be ok)
给你：（没有在 SQL2000 上测试，但应该没问题）
Generates 'disables':
生成“禁用”：
SELECT 'IF EXISTS (SELECT * FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N''[dbo].' + FK +''')
AND parent_object_id = OBJECT_ID(N''[dbo].' + PT + '''))
ALTER TABLE ' + PT + ' NOCHECK CONSTRAINT ' + FK + ';'
FROM
(SELECT
OBJECT_NAME(constraint_object_id) as FK,
OBJECT_NAME(parent_object_id) as PT
FROM [sys].[foreign_key_columns] ) T
ORDER BY FK
Generates 'enables':
生成“启用”：
SELECT 'ALTER TABLE ' + PT + ' WITH CHECK CHECK CONSTRAINT ' + FK + ';'
FROM
(SELECT
OBJECT_NAME(constraint_object_id) as FK,
OBJECT_NAME(parent_object_id) as PT
FROM [sys].[foreign_key_columns] ) T
ORDER BY FK
Update: Oops, I thought you wanted it for all tables :) You can just modify above for your single table.
更新：哎呀，我以为你想要所有表:) 你可以只为你的单个表修改上面的内容。
回答by Mayo
I think you'll find that there is no easy way to drop constraints on a table in SQL Server 2000. That said, there are plenty of people who have written scripts that can identify and remove/disable/recreate foreign key constraints. One example is at http://www.mssqltips.com/tip.asp?tip=1376- but I haven't tested it on SQL Server 2000.
我想您会发现在 SQL Server 2000 中删除表上的约束没有简单的方法。也就是说，有很多人编写了可以识别和删除/禁用/重新创建外键约束的脚本。一个例子是在http://www.mssqltips.com/tip.asp?tip=1376- 但我没有在 SQL Server 2000 上测试过它。
EDIT: Here is another examplethat generates drop/create scripts for you.
编辑：这是为您生成删除/创建脚本的另一个示例。
