删除外键未引用的行 [英] Remove rows NOT referenced by a foreign key

问题描述

这与此问题有关：

我有一个带有主键的表，并且我有几个表引用该主键（使用外键）。我需要从该表中删除行，在该表中其他任何表（以及其他一些约束）都未引用主键。

I have a table with a primary key, and I have several tables that reference that primary key (using foreign keys). I need to remove rows from that table, where the primary key isn't being referenced in any of those other tables (as well as a few other constraints).

例如：

Group
groupid | groupname
    1   |    'group 1'
    2   |    'group 3'
    3   |    'group 2'
    ... |    '...'

Table1
tableid | groupid | data
    1   |    3    |    ...
    ... |    ...  |    ...

Table2
tableid | groupid | data
    1   |    2    |    ...
    ... |    ...  |    ...

等。任何表格中都未引用组中的某些行，因此我需要删除这些行。除此之外，我还需要知道如何查找所有在Group中引用给定行的表/行。

and so on. Some of the rows in Group aren't referenced in any of the tables, and I need to remove those rows. In addition to this, I need to know how to find all of the tables/rows that reference a given row in Group.

我知道我可以查询每个表并检查groupid，但是由于它们是外键，因此我想有一种更好的方法。

I know that I can just query every table and check the groupid's, but since they are foreign keys, I imagine that there is a better way of doing it.

这是使用Postgresql 8.3。

This is using Postgresql 8.3 by the way.

推荐答案

从本质上讲，SQL Server不会为约束保留2向信息，因此您唯一的选择是执行操作如果要删除该行，服务器将在内部进行操作：检查所有其他表。

At the heart of it, SQL servers don't maintain 2-way info for constraints, so your only option is to do what the server would do internally if you were to delete the row: check every other table.

如果（并且首先要确定）约束是简单的检查，则不要携带任何在删除级联上类型的语句时，您可以尝试从组表中删除所有内容。因此，任何删除的行都将没有引用它。否则，您会被Quassnoi的答案所困扰。

If (and be damn sure first) your constraints are simple checks and don't carry any "on delete cascade" type statements, you can attempt to delete everything from your group table. Any row that does delete would thus have nothing reference it. Otherwise, you're stuck with Quassnoi's answer.

这篇关于删除外键未引用的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！