创建两个表TblTestA,TblTestB,删掉TblTestA
create table dbo.TblTestA(Idbigintprimary key identity(1, 1),namevarchar(20)unique,)create table dbo.TblTestB(Idbigintprimary key identity(1, 1),TestAIdbigintnot null foreign key references Statusvarchar(1),)
报错:it is referenced by a FOREIGN KEY constraint.
原因:其他表引用该表外键
解决:
1.查找该表被其他表引用的外键
exec sp_helpconstraint ‘表名’;
2.找到该关联外键约束表name
select name
from sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_id
where f.parent_object_id=object_id('外键关联表名' )
3.删掉外键约束
ALTER TABLE 外键约束表DROP CONSTRAINT name(上面查出的约束名)
4.删表(可以添加事件)
BEGIN TRYBEGIN TRANSACTIONALTER TABLE TblTestB DROP CONSTRAINT FK__TblTestB__TestAI__635CD8E4DROP TABLE dbo.TblTestACOMMIT TRANSACTIONPRINT 'commit sucesss'END TRYBEGIN CATCHROLLBACK TRANSACTIONPRINT 'rollback sucesss'END CATCH
如果觉得《sql server 删表外键约束报错》对你有帮助,请点赞、收藏,并留下你的观点哦!