如何删除数据库具有外键约束的副本 [英] How do I delete duplicate where a database has foreign key constraints

问题描述

数据库有重复记录问题，当我尝试删除时，有外键。

任何人请给我一些想法如何解决这个副本？

谢谢



我尝试过：



我没试过我发现它需要一个好的脚本

A database has duplicate record issue and when I try to remove, there is foreign key.
Any one please give me some ideas how to resolve this duplicate?
Thanks

What I have tried:

I didn't try I figure out it needs a good script

推荐答案

问题是重复的行通过外键包含在另一个表中：

用户

The problem is that the duplicated rows are included in another table via a foreign key:
Users

ID   Name
1    Joe
2    Mark
3    Joe

购买

Purchases

ID   UserID   Purchase
1    1        10.00
2    1        14.00
3    2        27.43
4    3        17.00

因此UserID列是Users表中ID列的外键。

因此，如果您尝试从Users中删除第二个Joe（ID = 3）SQL抱怨因为这会在Purchases表中留下孤立行 - 这意味着你的数据库是否定的更长的一致性。

您不能这样做：如果您在Users表中有重复项，您必须移动Purchases表中的所有相关记录，以便它们都与您的用户实例相关联在删除用户表中的备用条目之前保留。

这并不复杂 - 在上面的示例中它只是：

So the UserID column is a Foreign Key to the ID column in the Users table.
As a result, if you try to remove the second "Joe" from Users (ID = 3) SQL complains because that would leave "orphaned" rows in the Purchases table - and that means your Database is no longer consistent.
You can't do that: if you have duplicates in the Users table, you have to move all the dependent records in the Purchases table so they all relate the the instance of the user you are keeping before you delete the spare entries in the Users Table.
That's not complex - in the example above it's just:

UPDATE Purchases SET UserID=1 WHERE UserID=2

然后您可以毫无问题地执行DELETE。



您可以设置SQL来执行CASCADE DELETE，它会删除所有相关记录，但这很危险 - 特别是当你要删除它时，因为你不知道你要删除哪些数据以及它是否重要。

You can then perform your DELETE without problems.

You could set SQL to do a CASCADE DELETE where it removes all dependent records for you, but that's dangerous - particularly when it's a duplicate you are removing as you don't know what data you are about to delete and if it's important.

这篇关于如何删除数据库具有外键约束的副本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！