如何删除具有列作为SQL Server中其他表的外键的表中的行? [英] How to delete a row in a table which has a column as a foreign key to other tables in SQL Server?

问题描述

我有一个主表，该表的主键列由同一数据库中其他表中的许多列所引用，我想删除该表中的行而不必先删除其他表(具有对该表的引用值) .我可以在Access中简单地做到这一点，但在SQL Server中似乎有点棘手.
您能指导我如何实现这一目标吗?我喜欢简单性，我感觉到Access中有这种感觉，那时候我认为这就是为什么它们提供了所谓的外键"！
非常感谢！

I have a master table with a primary key column refered to by many columns in other tables in the same database, I want to delete a row in that table without having to delete others first (which have referent values to that table). I can simply do this in Access but it seems a bit tricky in SQL Server.
Could you please guide me through the how to achieve that? I like the simplicity, I felt how it is in Access and at that time I thought that''s why they provide the so-called "foreign key"!
Thank you very much!

推荐答案

使其变得困难的全部原因在于引用完整性.如果从父表中删除该行，则将修改其他表中的所有行，这将导致查询等问题.在关系上设置ON DELETE CASCADE也可以删除子表中的行.

http://msdn.microsoft.com/en-us/library/aa933119 (v = sql.80).aspx [

The whole point in making it difficult is because of referential entegrity. If you delete the row from the parent table then all rows in any other table would be ophaned and would cause problems with queries and such. Set ON DELETE CASCADE on the relationship to delete the rows in the child table as well.

http://msdn.microsoft.com/en-us/library/aa933119(v=sql.80).aspx[^]

ALTER TABLE MasterTable
ADD CONSTRAINT fk_xyz 
FOREIGN KEY (xyz) 
REFERENCES ChildTable (xyz) ON DELETE CASCADE

首先，您必须从子表中删除记录，然后从父表中删除.

First u have to delete the record from Child table then remove from parent table .

这篇关于如何删除具有列作为SQL Server中其他表的外键的表中的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！