sql - 约束一对多关系或使用预定义的关系约束创建表

Question

在我正在进行的项目中，我有一个表需要定义为具有一到九的关系，我想知道在数据库中创建该表的最佳方法是什么？我在 PostgreSQL 工作。

我最初的想法是创建一个表并明确创建链接(注意 actual_id 是因为系统我必须虚拟化 id 因为我需要唯一的表但我还需要知道模板的实际 id是)

CREATE TABLE template (
    id int,
    actual_id int,
    foreign_key0 int references other_table(id),
    foreign_key1 int references other_table(id),
    foreign_key2 int references other_table(id),
    foreign_key3 int references other_table(id),
    foreign_key4 int references other_table(id),
    foreign_key5 int references other_table(id),
    foreign_key6 int references other_table(id),
    foreign_key7 int references other_table(id),
    foreign_key8 int references other_table(id)
);

但是，当我想在不再引用任何内容时从引用表中清除数据时，这会产生一个问题。此外，我非常肯定这从一开始就是糟糕的数据库设计。

我的另一个想法是，我只制作具有一个约束的表

CREATE TABLE template (
    id int,
    actual_id int,
    foreign_key0 int references other_table(id) );

但是这里的问题是我如何限制它对另一个表只有 9 个引用？存储过程？以编程方式？

最终，如果我坚持使用第一种方法，我很确定我只需要将所有不同的 foreign_key 选择到另一个只有一个列的表中，并将其与 other_table 的 id 进行比较。我不想这样做。看起来真的很蠢。我真的很想用第二种方式，但我不知道如何最好地去做。

Answer 1

1:n 关系总是可以颠倒为 n:1 。换句话说，而不是:

parent:field1 -> child1:id
parent:field2 -> child2:id
parent:field3 -> child3:id
....
parent:field9 -> child9

你总是可以这样写:

child1:parent_id -> parent:id
child2:parent_id -> parent:id
child3:parent_id -> parent:id
....
child9:parent_id -> parent:id

...并通过触发器或在应用程序中限制每个 parent 的 child 数量。这是我强烈推荐的方法。您将需要一个可延迟的约束触发器以允许您插入任何内容。

如果要在数据库中强制执行，请使用约束触发器。给定虚拟模式:

CREATE TABLE parent (id serial primary key);
CREATE TABLE child( id serial primary key, parent_id integer references parent(id) );
INSERT INTO parent (id) values ( DEFAULT );
INSERT INTO child ( parent_id ) 
SELECT p.id FROM parent p CROSS JOIN generate_series(1,9) x;

你可以这样写:

CREATE OR REPLACE FUNCTION children_per_parent() RETURNS TRIGGER AS $$
DECLARE
    n integer;
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        SELECT INTO n count(id) FROM child WHERE parent_id = NEW.parent_id;
        IF n <> 9 THEN
            RAISE EXCEPTION 'During % of child: Parent id=% must have exactly 9 children, not %',tg_op,NEW.parent_id,n;
        END IF;
    END IF;

    IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
        SELECT INTO n count(id) FROM child WHERE parent_id = OLD.parent_id;
        IF n <> 9 THEN
            RAISE EXCEPTION 'During % of child: Parent id=% must have exactly 9 children, not %',tg_op,NEW.parent_id,n;
        END IF;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

CREATE CONSTRAINT TRIGGER children_per_parent_tg
AFTER INSERT OR UPDATE OR DELETE ON child
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE children_per_parent();

CREATE OR REPLACE parent_constrain_children() RETURNS trigger AS $$
DECLARE 
    n integer;
BEGIN
    IF TG_OP = 'INSERT' THEN
        SELECT INTO n count(id) FROM child WHERE parent_id = NEW.id;
        IF n <> 9 THEN
            RAISE EXCEPTION 'During INSERT of parent id=%: Must have 9 children, found %',NEW.id,n;
        END IF;
    END IF;
    -- No need for an UPDATE or DELETE check, as regular referential integrity constraints
    -- and the trigger on `child' will do the job.
    RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';


CREATE CONSTRAINT TRIGGER parent_limit_children_tg
AFTER INSERT ON parent
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE parent_constrain_children();

请注意，上面有两个触发器。对 child 的触发是显而易见的。需要父触发器以防止插入没有任何子项的父项。

现在观察一个测试:

regress=# delete from child;
ERROR:  During DELETE: Parent id 1 must have exactly 9 children, not 0
regress=# insert into child( parent_id) SELECT id FROM parent;
ERROR:  During INSERT: Parent id 1 must have exactly 9 children, not 10

因为延迟约束触发器是在事务提交时检查的，不是立即或在语句结束时，你仍然可以这样做:

regress# BEGIN;
BEGIN
regress# INSERT INTO parent (id) values ( DEFAULT ) RETURNING id;
 id 
----
  2
INSERT 0 1
regress# insert into child ( parent_id ) SELECT p.id FROM parent p CROSS JOIN generate_series(1,9) x WHERE p.id = 4;
INSERT 0 9
regress# COMMIT;
COMMIT

...但是如果您将“generate_series”最大值更改为 8 或 10，或者完全不插入任何子项，COMMIT 将失败，例如:

regress=# commit;
ERROR:  During INSERT: Parent id 5 must have exactly 9 children, not 8

如果您只要求每个父项有一个最多 9 个子项，而不是刚好在上述触发器中实现的 9 个子项，您可以删除 DEFERRABLE INITIALLY DEFERRED , 更改 <> 9至 <= 9 , 然后去掉 DELETE child 中的处理程序扳机。

---

顺便说一句，如果我在 Java 中使用 JPA 或其他一些相当聪明的 ORM，我只会限制父级子集合的大小:

@Entity
public Parent {

    @Column
    @Size(min=9,max=9)
    private Collection<Child> collectionOfChildren;

}

虽然没有在数据库级别强制执行，但更简单。