我有两个表：" foos"和" bars"，它们之间存在多对一的关系：每个" foo"可以有很多" bars"。我还有一个视图foobars，它连接了这两个表(它的查询就像select foo。*，foos中的bar.id，bar.foo_id = foo.id的酒吧)。

编辑：如果我们说foos和bars之间存在多对多关系，那我们就不会错。但是，" bar"只是一个标签(实际上，它是一个大小)，并仅由其名称组成。表" bars"具有与链接表相同的作用。

我有一条插入foobars的规则，这样会将foo部分作为新行插入到foos中，并将由几个以逗号分隔的bar-id组成的bar部分分割开，对于每个这样的部分在它和适当的foo之间创建了一个链接(我使用一个过程来做到这一点)。

这非常适合插入。但是，当涉及到更新整个事情时，我遇到了一个问题。规则的foo部分很容易。但是，我不知道如何处理多个bar部分。当我尝试执行规则中的DELETE FROM bars WHERE foo_id = new.foo_id时，我结束了删除表bars中的所有内容。

我究竟做错了什么？有没有办法实现我所需要的？最后，我对整件事的处理是否明智？

(我在视图中这样做太复杂了，因为我得到的数据是foo及其所有bars的形式，但是用户只能看到foobars。)

解决方案

我不认为new.foo_id在删除的上下文中是正确的。

它不应该从foo_id = old.foo_id的酒吧删除吗？

Rysiek，如果我理解正确的话，我们会在" foos"表中有一个文本列，该文本列经过解析以提取指向" bars"表的外键。在某些情况下，这种建立关系的方法可能是合理的，但是几乎所有有关数据库编程的指南/教程都不会这样做。为什么不在bars中使用标准外键，而在foos中指向foo呢？除非有必要将bar分配给多个foo。如果是这样，这会将关系确定为多对多而不是一对多。在这两种情况下，使用标准的基于外键的解决方案对于数据库而言似乎更为自然。

一对多关系的示例数据库模式：

CREATE TABLE foos (
    id SERIAL PRIMARY KEY,
    ....
);
CREATE TABLE bars (
    id SERIAL PRIMARY KEY,
    foo_id INT REFERENCES bars (id) ON DELETE CASCADE,
    ...
);

对于多对多关系也是如此：

CREATE TABLE foos (
    id SERIAL PRIMARY KEY,
    ....
);
CREATE TABLE bars (
    id SERIAL PRIMARY KEY,
    ...
);
CREATE TABLE foostobars (
    foo_id INT REFERENCES foos (id) ON DELETE CASCADE,
    bar_id INT REFERENCES bars (id) ON DELETE CASCADE
);

我还建议使用INNER JOIN代替表乘法(SELECT FROM foos，bars)。

CREATE VIEW foobars AS
SELECT
    foos.id AS foo_id, foos.something,
    bars.id AS bar_id, bars.somethingelse
FROM foos
INNER JOIN bars ON bars.foo_id = foo.id;

多对多INNER JOINS相同

CREATE VIEW foobars AS
SELECT
    foos.id AS foo_id, foos.something,
    bars.id AS bar_id, bars.somethingelse
FROM foos
INNER JOIN foostobars AS ftb ON ftb.foo_id = foo.id
INNER JOIN bars ON bars.id = ftb.bar_id;

这就是我实际处理的方式：当我遇到独特的约束违例时，我没有删除而不是更新，而是简单地删除了" foo"并让级联处理了" bars"。然后，我只是尝试再次插入。我必须使用多个SQL语句来执行此操作，但它似乎有效。

删除问题是我们要删除的谓词不基于要删除的表。我们需要基于连接谓词删除。这看起来像这样：

delete b
  from foo f
  join foobar fb
    on f.FooID = fb.FooID
  join bar b
    on b.BarId = fb.BarID
 where f.FooID = 123

这使我们可以保存Foo's表，Bar's表和记录Bar's Foo所具有的联接表。我们无需组成列表并将它们分开。这是一件坏事，因为查询优化器无法使用索引来标识相关记录，实际上这违反了1NF"无重复组"规则。正确的架构如下所示：

Create table Foo (
     FooID int
    ,[Other Foo attributes]
)

Create table Bar (
     BarID int
    ,[Other Bar attributes]
)

Create table FooBar (
     FooID int
    ,BarID int
)

使用适当的索引，可以将M：M关系存储在FooBar中，而DBMS可以在其本机数据结构中有效地存储和处理该关系。