SQL DROP TABLE外键约束 [英] SQL DROP TABLE foreign key constraint

问题描述

如果我想像这样删除我的数据库中的所有表，它会照顾外键约束吗？

If I want to delete all the tables in my database like this, will it take care of the foreign key constraint? If not, how do I take care of that first?

GO
IF OBJECT_ID('dbo.[Course]','U') IS NOT NULL
    DROP TABLE dbo.[Course]
GO
IF OBJECT_ID('dbo.[Student]','U') IS NOT NULL
    DROP TABLE dbo.[Student]

推荐答案

要获取所有的外键关系引用你的表，你可以使用这个SQL（如果你在SQL Server 2005和更高版本）：

To get all foreign key relationships referencing your table, you could use this SQL (if you're on SQL Server 2005 and up):

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

并且如果有，使用此语句可以创建SQL语句实际上删除那些FK关系：

and if there are any, with this statement here, you could create SQL statements to actually drop those FK relations:

SELECT 
    'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    '.[' + OBJECT_NAME(parent_object_id) + 
    '] DROP CONSTRAINT ' + name
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

这篇关于SQL DROP TABLE外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！