如何在SQL Server中删除外键?

我创建了一个外键(在SQL Server)通过:

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

然后我运行这个查询:

alter table company drop column CountryID;

我得到这个错误:

信息5074,16层，状态4,2号线对象'Company_CountryID_FK'依赖于列'CountryID'。编号4922,16层，9号州，2号线 ALTER TABLE DROP COLUMN CountryID失败，因为有一个或多个对象访问该列

我试过了，但似乎并不奏效:

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

我需要做什么才能删除CountryID列?

谢谢。

当前回答

Try

alter table company drop constraint Company_CountryID_FK


alter table company drop column CountryID

2008-09-18 14:54:18

其他回答

Try

alter table company drop constraint Company_CountryID_FK


alter table company drop column CountryID

2008-09-18 14:54:18

你是想放弃FK约束还是列本身?

删除约束:

alter table company drop constraint Company_CountryID_FK

在删除约束之前，您将无法删除列。

2013-03-26 13:34:53

首先检查约束是否存在，然后删除它。

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

2014-06-13 22:23:54

我想这会对你有帮助……

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)

它会根据特定的表和列删除外键约束。

2013-12-31 12:45:01

我不知道MSSQL，但它不是:

alter table company drop **constraint** Company_CountryID_FK;

2008-09-18 14:56:00

如何在SQL Server中删除外键?

推荐文章

最新文章

标签