我有一个表person
和两个表foo
和bar
,均用名为person_id
的外键引用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小提琴:http://sqlfiddle.com/#!17/40c78/3
您可以在包含id
以及person_id
的foo
和bar
上创建一个唯一的约束。如果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
删除原始的外键约束。
i将不是使用foo_bar
的人工主键,因为(foo_id, bar_id)
是一种天然主键,可以保证不会输入不止一次的关系。
您已经偶然发现了单个替代键的主要问题:当涉及层次结构时(例如,foo_bar是孩子和bar and Bar and conthor to person的孩子),数据库系统无法执行一致性。
因此,请改用复合键。(伪代码)的线条:
CREATE TABLE person (person_nr, name text,
PRIMARY KEY (person_nr));
CREATE TABLE foo (person_nr, foo_nr, comment text,
PRIMARY KEY (person_nr, foo_nr),
FOREIGN KEY person_nr REFERENCES person(person_nr));
CREATE TABLE bar (person_nr, bar_nr, comment text,
PRIMARY KEY (person_nr, bar_nr),
FOREIGN KEY person_nr REFERENCES person(person_nr));
CREATE TABLE foo_bar (person_id, foo_nr, bar_nr,
PRIMARY KEY (person_nr, foo_nr, bar_nr),
FOREIGN KEY (person_nr, foo_nr) REFERENCES foo(person_nr, foo_nr),
FOREIGN KEY (person_nr, bar_nr) REFERENCES bar(person_nr, bar_nr));
复合键的缺点是使加入更容易出现错误(即,您可能会混淆密钥部分或错过密钥的一部分),但它们也通过执行一致性使数据库更好。