仅当 sql server 中存在外键约束时，如何删除它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/482885/
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 a foreign key constraint only if it exists in sql server?
提问by solrevdev
I can drop a table if it exists using the following code but do not know how to do the same with a constraint:
如果表存在，我可以使用以下代码删除它，但不知道如何使用约束执行相同操作：
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TableName') AND type = (N'U')) DROP TABLE TableName
go
I also add the constraint using this code:
我还使用以下代码添加约束：
ALTER TABLE [dbo].[TableName]
WITH CHECK ADD CONSTRAINT [FK_TableName_TableName2] FOREIGN KEY([FK_Name])
REFERENCES [dbo].[TableName2] ([ID])
go
回答by James L
The more simple solution is provided in Eric Isaacs'sanswer. However, it will find constraints on any table. If you want to target a foreign key constraint on a specific table, use this:
Eric Isaacs 的回答中提供了更简单的解决方案。但是，它会在任何表上找到约束。如果要针对特定表的外键约束，请使用以下命令：
IF EXISTS (SELECT *
FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N'dbo.FK_TableName_TableName2')
AND parent_object_id = OBJECT_ID(N'dbo.TableName')
)
ALTER TABLE [dbo.TableName] DROP CONSTRAINT [FK_TableName_TableName2]
回答by Eric Isaacs
This is a lot simpler than the current proposed solution:
这比当前提出的解决方案简单得多：
IF (OBJECT_ID('dbo.FK_ConstraintName', 'F') IS NOT NULL)
BEGIN
ALTER TABLE dbo.TableName DROP CONSTRAINT FK_ConstraintName
END
If you need to drop another type of constraint, these are the applicable codes to pass into the OBJECT_ID() function in the second parameter position:
如果您需要删除另一种类型的约束，这些是在第二个参数位置传递到 OBJECT_ID() 函数的适用代码：
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
UQ = UNIQUE constraint
You can also use OBJECT_ID without the second parameter.
您也可以使用不带第二个参数的 OBJECT_ID。
Full List of types here:
类型的完整列表在这里：
Object type:
对象类型：
AF = Aggregate function (CLR) C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint FN = SQL scalar function FS = Assembly (CLR) scalar-function FT = Assembly (CLR) table-valued function IF = SQL inline table-valued function IT = Internal table P = SQL Stored Procedure PC = Assembly (CLR) stored-procedure PG = Plan guide PK = PRIMARY KEY constraint R = Rule (old-style, stand-alone) RF = Replication-filter-procedure S = System base table SN = Synonym SO = Sequence object
AF = Aggregate function (CLR) C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint FN = SQL scalar function FS = Assembly (CLR) scalar-function FT = Assembly (CLR) table-valued function IF = SQL inline table-valued function IT = Internal table P = SQL Stored Procedure PC = Assembly (CLR) stored-procedure PG = Plan guide PK = PRIMARY KEY constraint R = Rule (old-style, stand-alone) RF = Replication-filter-procedure S = System base table SN = Synonym SO = Sequence object
Applies to: SQL Server 2012 through SQL Server 2014.
适用于：SQL Server 2012 到 SQL Server 2014。
SQ = Service queue TA = Assembly (CLR) DML trigger TF = SQL table-valued-function TR = SQL DML trigger TT = Table type U = Table (user-defined) UQ = UNIQUE constraint V = View X = Extended stored procedure
SQ = Service queue TA = Assembly (CLR) DML trigger TF = SQL table-valued-function TR = SQL DML trigger TT = Table type U = Table (user-defined) UQ = UNIQUE constraint V = View X = Extended stored procedure
回答by Jovan MSFT
In SQL Server 2016 you can use DROP IF EXISTS:
在 SQL Server 2016 中，您可以使用 DROP IF EXISTS：
CREATE TABLE t(id int primary key,
parentid int
constraint tpartnt foreign key references t(id))
GO
ALTER TABLE t
DROP CONSTRAINT IF EXISTS tpartnt
GO
DROP TABLE IF EXISTS t
回答by DevDave
IF (OBJECT_ID('DF_Constraint') IS NOT NULL)
BEGIN
ALTER TABLE [dbo].[tableName]
DROP CONSTRAINT DF_Constraint
END
回答by Sam Saffron
James's answerworks just fine if you know the name of the actual constraint. The tricky thing is that in legacy and other real world scenarios you may not knowwhat the constraint is called.
如果您知道实际约束的名称，James 的回答就很好用。棘手的是，在遗留和其他现实世界场景中，您可能不知道约束被称为什么。
If this is the case you risk creating duplicate constraints, to avoid you can use:
如果是这种情况，您可能会面临创建重复约束的风险，为了避免您可以使用：
create function fnGetForeignKeyName
(
@ParentTableName nvarchar(255),
@ParentColumnName nvarchar(255),
@ReferencedTableName nvarchar(255),
@ReferencedColumnName nvarchar(255)
)
returns nvarchar(255)
as
begin
declare @name nvarchar(255)
select @name = fk.name from sys.foreign_key_columns fc
join sys.columns pc on pc.column_id = parent_column_id and parent_object_id = pc.object_id
join sys.columns rc on rc.column_id = referenced_column_id and referenced_object_id = rc.object_id
join sys.objects po on po.object_id = pc.object_id
join sys.objects ro on ro.object_id = rc.object_id
join sys.foreign_keys fk on fk.object_id = fc.constraint_object_id
where
po.object_id = object_id(@ParentTableName) and
ro.object_id = object_id(@ReferencedTableName) and
pc.name = @ParentColumnName and
rc.name = @ReferencedColumnName
return @name
end
go
declare @name nvarchar(255)
declare @sql nvarchar(4000)
-- hunt for the constraint name on 'Badges.BadgeReasonTypeId' table refs the 'BadgeReasonTypes.Id'
select @name = dbo.fnGetForeignKeyName('dbo.Badges', 'BadgeReasonTypeId', 'dbo.BadgeReasonTypes', 'Id')
-- if we find it, the name will not be null
if @name is not null
begin
set @sql = 'alter table Badges drop constraint ' + replace(@name,']', ']]')
exec (@sql)
end
回答by Mitch Wheat
ALTER TABLE [dbo].[TableName]
DROP CONSTRAINT FK_TableName_TableName2
回答by Rajalingam
Declare @FKeyRemoveQuery NVarchar(max)
IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'dbo.TableName'))
BEGIN
SELECT @FKeyRemoveQuery='ALTER TABLE dbo.TableName DROP CONSTRAINT [' + LTRIM(RTRIM([name])) + ']'
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID(N'dbo.TableName')
EXECUTE Sp_executesql @FKeyRemoveQuery
END
回答by Samir Savasani
I think this will helpful to you...
我想这对你有帮助...
DECLARE @ConstraintName nvarchar(200)
SELECT
@ConstraintName = KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON KCU.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE
KCU.TABLE_NAME = 'TABLE_NAME' AND
KCU.COLUMN_NAME = 'TABLE_COLUMN_NAME'
IF @ConstraintName IS NOT NULL EXEC('alter table TABLE_NAME drop CONSTRAINT ' + @ConstraintName)
It will delete foreign Key Constraint based on specific table and column.
它将根据特定的表和列删除外键约束。
回答by Ardalan Shahgholi
You can use those queries to find all FKs for your table.
您可以使用这些查询来查找表的所有 FK。
Declare @SchemaName VarChar(200) = 'Schema Name'
Declare @TableName VarChar(200) = 'Table name'
-- Find FK in This table.
SELECT
'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].['
+ OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +
'ALTER TABLE ' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
'.[' + OBJECT_NAME(FK.parent_object_id) +
'] DROP CONSTRAINT ' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.parent_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName
-- Find the FKs in the tables in which this table is used
SELECT
' IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + FK.name + ']'
+ ''') AND parent_object_id = OBJECT_ID(N''' +
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].['
+ OBJECT_NAME(FK.parent_object_id) + ']' + ''')) ' +
' ALTER TABLE ' + OBJECT_SCHEMA_NAME(FK.parent_object_id) +
'.[' + OBJECT_NAME(FK.parent_object_id) +
'] DROP CONSTRAINT ' + FK.name
, S.name , O.name, OBJECT_NAME(FK.parent_object_id)
FROM sys.foreign_keys AS FK
INNER JOIN Sys.objects As O
ON (O.object_id = FK.referenced_object_id )
INNER JOIN SYS.schemas AS S
ON (O.schema_id = S.schema_id)
WHERE
O.name = @TableName
And S.name = @SchemaName
回答by Melbourne Developer
The accepted answer on this question doesn't seem to work for me. I achieved the same thing with a slightly different method:
关于这个问题的公认答案似乎对我不起作用。我用稍微不同的方法实现了同样的目的：
IF (select object_id from sys.foreign_keys where [name] = 'FK_TableName_TableName2') IS NOT NULL
BEGIN
ALTER TABLE dbo.TableName DROP CONSTRAINT FK_TableName_TableName2
END
