Как эмулировать тегированный союз в базе данных?

Каков наилучший способ эмуляции Tagged union в базах данных? Я говорю о чем-то вроде этого:

create table t1 {
  vehicle_id INTEGER NOT NULL REFERENCES car(id) OR motor(id) -- not valid
  ...
}

где vehicle_id будет идентификатором в таблице автомобилей или моторным столом, и он будет знать, что.

(предположим, что автомобильные и автомобильные столы не имеют ничего общего0

Ответ 1

Некоторые люди используют проект Polymorphic Associations для этого, позволяя vehicle_id содержать значение, существующее в таблицах car или motor. Затем добавьте vehicle_type, который называет таблицу, указанную в строке t1.

Проблема заключается в том, что вы не можете объявить реальное ограничение внешнего ключа SQL, если вы это сделаете. В SQL нет поддержки для внешнего ключа с несколькими ссылочными целями. Есть и другие проблемы, но отсутствие ссылочной целостности уже является нарушителем сделки.

Лучшим дизайном является заимствование концепции из OO-дизайна общего супертипа как car, так и motor:

CREATE TABLE Identifiable (
 id SERIAL PRIMARY KEY
);

Затем сделайте t1 ссылкой на эту таблицу супертипа:

CREATE TABLE t1 (
  vehicle_id INTEGER NOT NULL,
  FOREIGN KEY (vehicle_id) REFERENCES identifiable(id)
  ...
);

А также сделайте подтипы ссылкой на их родительский супертип. Обратите внимание, что первичный ключ подтипов не будет автоматически увеличиваться. Родительский супертип заботится о назначении нового значения id, а дети ссылаются только на это значение.

CREATE TABLE car (
  id INTEGER NOT NULL,
  FOREIGN KEY (id) REFERENCES identifiable(id)
  ...
);

CREATE TABLE motor (
  id INTEGER NOT NULL,
  FOREIGN KEY (id) REFERENCES identifiable(id)
  ...
);

Теперь вы можете иметь истинную ссылочную целостность, но также поддерживать несколько таблиц подтипов со своими атрибутами.

В ответе @Quassnoi также показан метод принудительного применения непересекающихся подтипов. То есть вы хотите, чтобы оба car и motor ссылались на одну и ту же строку в их родительской таблице супертипов. Когда я это делаю, я использую первичный ключ с одним столбцом для Identifiable.id, но также объявляю ключ UNIQUE по Identifiable.(id, type). Внешние ключи в car и motor могут ссылаться на уникальный ключ с двумя столбцами вместо первичного ключа.

Ответ 2

CREATE TABLE vehicle (type INT NOT NULL, id INT NOT NULL,
             PRIMARY KEY (type, id)
)

CREATE TABLE car (type INT NOT NULL DEFAULT 1, id INT NOT NULL PRIMARY KEY,
             CHECK(type = 1),
             FOREIGN KEY (type, id) REFERENCES vehicle
)

CREATE TABLE motorcycle (type INT NOT NULL DEFAULT 2, id INT NOT NULL PRIMARY KEY,
             CHECK(type = 2),
             FOREIGN KEY (type, id) REFERENCES vehicle
)

CREATE TABLE t1 (
  ...
  vehicle_type INT NOT NULL,
  vehicle_id INT NOT NULL,
  FOREIGN KEY (vehicle_type, vehicle_id) REFERENCES vehicle
  ...
)

Ответ 3

Я думаю, вы могли бы моделировать такую ссылку, используя наследование таблицы в PostgreSQL.

Если вам действительно нужно знать, откуда в Query входит строка, вы можете использовать простой элемент UNION ALL (эта возможность не имеет ничего общего с наследованием таблицы):

SELECT car.*, 'car' table_name
UNION ALL
SELECT motor.*, 'motor' table_name