Блог

У меня есть одна таблица person и две таблицы foo , bar и person обе ссылаются person_id на внешние ключи с именованными в них именами. ,,, Мне нужно создать таблицу связи, которая связывает одну 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 ссылается на данные как для Джона, так и для Джейн:

 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

Вы могли бы создать уникальное ограничение для foo и bar , которое содержит id так же, как и person_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) это естественный первичный ключ, который гарантирует, что никакая связь не вводится более одного раза.

Вы столкнулись с основной проблемой с одиночными суррогатными ключами: когда дело доходит до иерархий (например, foo_bar является дочерним для foo и bar, которые оба являются дочерними для 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));
  

Недостатком составных ключей является то, что соединения немного более подвержены ошибкам (т. Е. вы можете перепутать ключевые части или пропустить часть ключа), но они также улучшают базу данных, обеспечивая согласованность.