首页 / 知识

关于tsql：如何在SQL Server中删除外键？

2023-04-17 04:36:00

How do I drop a foreign key in SQL Server?

我通过以下方式创建了外键(在SQL Server中)：

1
2
3

alter table company add CountryID varchar(3);
alter table company add constraint Company_CountryID_FK foreign key(CountryID)
references Country;

然后，我运行以下查询：

1	alter table company drop column CountryID;

我得到这个错误：

Msg 5074, Level 16, State 4, Line 2
The object 'Company_CountryID_FK' is dependent on column 'CountryID'.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE DROP COLUMN CountryID failed because one or more objects access this column

我已经尝试过了，但是似乎没有用：

1 2	alter table company drop foreign key Company_CountryID_FK; alter table company drop column CountryID;

我需要怎么做来删除CountryID列？

谢谢。

尝试

1
2
3
4

alter table company drop constraint Company_CountryID_FK

alter table company drop column CountryID

这将起作用：

1	ALTER TABLE [dbo].[company] DROP CONSTRAINT [Company_CountryID_FK]

我认为这对您有帮助...

1
2
3
4
5
6
7
8
9
10
11
12

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)

它将基于特定的表和列删除外键约束。

首先检查约束的存在，然后将其删除。

1
2
3
4

if exists (select 1 from sys.objects where name = 'Company_CountryID_FK' and type='F')
begin
alter table company drop constraint Company_CountryID_FK
end