将外键关系限制为相关子类型的行

Question

概述：我试图表示数据库中几种类型的实体，它们具有许多共同的基本字段，然后每个实体都具有一些其他字段，这些字段不与其他类型的实体共享。工作流通常会涉及将实体一起列出，因此我决定要有一个带有其公共字段的表，然后每个实体都将具有自己的表及其附加字段。

要实现：所有实体都拥有一个共同的领域“
tatus”；但是，某些实体将仅支持所有可能状态的子集。我还希望每种类型的实体都强制使用其状态子集。最后，我还将希望在将实体一起列出时将其包括在内，因此将其从一组通用字段中排除似乎是不正确的，因为这将需要特定类型表的并集，并且在其中缺少``实现接口’‘。
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);
*/

Answer 1

简化基于[`MATCH

SIMPLE`](http://www.postgresql.org/docs/current/interactive/sql-
createtable.html)fk约束的行为

如果至少一列具有默认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列（主要实体）：

• 使用postgresql DB需要多少磁盘空间来存储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

等等。

相关答案：

• 完全匹配与简单匹配
• 仅当第三列不是NULL时，两列外键约束
• 当验证在另一个表上有条件时，数据库中的唯一性验证

保留所有桌子

如果出于某种原因不需要所有四个表，请考虑对dba.SE上一个非常类似的问题进行详细的解决：

• 强制约束“远离桌子”。

遗产

…可能是您描述的另一种选择。如果您可以忍受一些主要限制。相关答案：

• 在PostgreSQL中创建两种类型的表