Ограничить отношение внешнего ключа к строкам связанных подтипов

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

Для реализации: есть общее поле "статус", которое есть у всех сущностей; однако некоторые объекты будут поддерживать только подмножество всех возможных статусов. Я также хочу, чтобы каждый тип объекта принудительно использовал свое подмножество статусов. Наконец, я также хочу включить это поле при выводе списка сущностей вместе, поэтому исключение его из набора общих полей кажется некорректным, так как для этого потребуется объединение таблиц определенного типа и отсутствие "реализующего интерфейса" в средствах SQL что включение этого поля было бы условным.

Почему я здесь: Ниже приведено функциональное решение, но мне интересно, есть ли лучший или более распространенный способ решения проблемы. В частности, тот факт, что это решение требует от меня сделать излишним unique Ограничение и избыточное поле статуса кажутся неэлегансными.

create schema test;

create table test.statuses(
    id      integer     primary key
);
create table test.entities(
    id      integer     primary key,
    status  integer,
    unique(id, status),
    foreign key (status) references test.statuses(id)
);

create table test.statuses_subset1(
    id      integer     primary key,
    foreign key (id) references test.statuses(id)
);
create table test.entites_subtype(
    id integer primary key,
    status integer,
    foreign key (id) references test.entities(id),
    foreign key (status) references test.statuses_subset1(id),
    foreign key (id, status) references test.entities(id, status) initially deferred
);

Некоторые данные:

insert into test.statuses(id) values
    (1),
    (2),
    (3);
insert into test.entities(id, status) values
    (11, 1),
    (13, 3);
insert into test.statuses_subset1(id) values
    (1), (2);
insert into test.entites_subtype(id, status) values
    (11, 1);

-- Test updating subtype first
update test.entites_subtype
    set status = 2
    where id = 11;
update test.entities
    set status = 2
    where id = 11;

-- Test updating base type first
update test.entities
    set status = 1
    where id = 11;
update test.entites_subtype
    set status = 1
    where id = 11;

/* -- This will fail
insert into test.entites_subtype(id, status) values
    (12, 3);
*/
Источник

1 ответ

Решение

Упростить построение на MATCH SIMPLE поведение фк ограничений

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

CREATE SCHEMA test;

CREATE TABLE test.status(
   status_id  integer PRIMARY KEY
  ,sub        bool NOT NULL DEFAULT FALSE  -- TRUE .. *can* be sub-status
  ,UNIQUE (sub, status_id)
);

CREATE TABLE test.entity(
   entity_id  integer PRIMARY KEY
  ,status_id  integer REFERENCES test.status  -- can reference all statuses
  ,sub        bool      -- see examples below
  ,additional_col1 text -- should be NULL for main entities
  ,additional_col2 text -- should be NULL for main entities
  ,FOREIGN KEY (sub, status_id) REFERENCES test.status(sub, status_id)
     MATCH SIMPLE ON UPDATE CASCADE  -- optionally enforce sub-status
);

Хранить некоторые дополнительные столбцы NULL (для основных объектов) очень дешево:

Кстати, за документацию:

Если refcolumn список опущен, первичный ключ reftable используется.

Демо-данные:

INSERT INTO test.status VALUES
  (1, TRUE)
, (2, TRUE)
, (3, FALSE);     -- not valid for sub-entities

INSERT INTO test.entity(entity_id, status_id, sub) VALUES
  (11, 1, TRUE)   -- sub-entity (can be main, UPDATES to status.sub cascaded)
, (13, 3, FALSE)  -- entity  (cannot be sub,  UPDATES to status.sub cascaded)
, (14, 2, NULL)   -- entity  (can    be sub,  UPDATES to status.sub NOT cascaded)
, (15, 3, NULL)   -- entity  (cannot be sub,  UPDATES to status.sub NOT cascaded)

SQL Fiddle (включая ваши тесты).

Альтернатива с одним FK

Другим вариантом будет ввести все комбинации (status_id, sub) в status стол (может быть только 2 на status_id) и иметь только одно ограничение fk:

CREATE TABLE test.status(
   status_id  integer
  ,sub        bool DEFAULT FALSE
  ,PRIMARY KEY (status_id, sub)
);

CREATE TABLE test.entity(
   entity_id  integer PRIMARY KEY
  ,status_id  integer NOT NULL  -- cannot be NULL in this case
  ,sub        bool NOT NULL     -- cannot be NULL in this case
  ,additional_col1 text
  ,additional_col2 text
  ,FOREIGN KEY (status_id, sub) REFERENCES test.status
     MATCH SIMPLE ON UPDATE CASCADE  -- optionally enforce sub-status
);

INSERT INTO test.status VALUES
  (1, TRUE)       -- can be sub ...
  (1, FALSE)      -- ... and main
, (2, TRUE)
, (2, FALSE)
, (3, FALSE);     -- only main

И т.п.

Связанные ответы:

Держи все столы

Если по каким-то причинам вам не нужны все четыре таблицы, рассмотрите подробное решение очень похожего вопроса на dba.SE:

наследование

... может быть другой вариант для того, что вы описываете. Если вы можете жить с некоторыми основными ограничениями. Соответствующий ответ:

Источник
Другие вопросы по тегам