gpt4 book ai didi

sql - 如何强制两个子表之间的耦合表引用同一个主表?

转载 作者:搜寻专家 更新时间:2023-10-30 22:24:34 24 4
gpt4 key购买 nike

我有一个表 person 和两个表 foobar 都引用了 person 外键命名人员编号。我需要创建一个耦合表,将一个 foo 链接到一个 bar,但两者都需要引用相同的 person

如何仅使用声明性结构在关系结构中表达这一点?或者我是否需要创建一个触发器来执行此操作?

CREATE TABLE person
(id int primary key, name text);

INSERT INTO person
(id, name)
VALUES
(1, 'John'),
(2, 'Jane');

CREATE TABLE foo
(id int primary key, person_id int references person(id) not null, comment text);

INSERT INTO foo
(id, person_id, comment)
VALUES
(1, 1, 'John is great'),
(2, 2, 'Jane is great');

CREATE TABLE bar
(id int primary key, person_id int references person(id) not null, comment text);

INSERT INTO bar
(id, person_id, comment)
VALUES
(1, 1, 'John is super great'),
(2, 2, 'Jane is super great');

CREATE TABLE foo_bar
(id int primary key, foo_id int references foo(id), bar_id int references bar(id));

INSERT INTO foo_bar
(id, foo_id, bar_id)
VALUES
(1, 1, 1),
(2, 1, 2), -- Shouldn't be possible!
(3, 2, 1), -- Shouldn't be possible!
(4, 2, 2);

如该查询所示,完全有可能在 foo_bar 中的一行引用 John 和 Jane 的数据的情况下获得结果:

select foo.comment, bar.comment from foo_bar
inner join foo ON foo.id = foo_bar.foo_id
inner join bar ON bar.id = foo_bar.bar_id;

结果:

John is great, John is super great
John is great, Jane is super great
Jane is great, John is super great
Jane is great, Jane is super great

SQL fiddle :http://sqlfiddle.com/#!17/40c78/3

最佳答案

您可以在 foobar 上创建一个包含 idperson_id 的唯一约束。如果 foo_bar 上的外键约束引用这些唯一约束,则自动满足条件。

ALTER TABLE foo ADD CONSTRAINT foo_id_person_unique
UNIQUE (person_id, id);
ALTER TABLE bar ADD CONSTRAINT bar_id_person_unique
UNIQUE (person_id, id);

ALTER TABLE foo_bar ADD person_id integer;

UPDATE foo_bar
SET person_id = foo.person_id
FROM foo
WHERE foo_bar.foo_id = foo_id;

ALTER TABLE foo_bar ALTER person_id SET NOT NULL;

ALTER TABLE foo_bar ADD CONSTRAINT foo_bar_foo_fkey
FOREIGN KEY (person_id, foo_id) REFERENCES foo (person_id, id);
ALTER TABLE foo_bar ADD CONSTRAINT foo_bar_bar_fkey
FOREIGN KEY (person_id, bar_id) REFERENCES bar (person_id, id);

然后从 foo_bar 中删除原始外键约束。

不会foo_bar使用人工主键,因为(foo_id, bar_id)是一个自然主键,可以保证没有多次输入关系。

关于sql - 如何强制两个子表之间的耦合表引用同一个主表?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55163634/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com