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

2023-12-04

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

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

简化构建MATCH SIMPLEfk 约束的行为

如果多列外部约束中至少有一列具有默认值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
);

It is 非常便宜存储一些额外的 NULL 列（对于主要实体）：

使用 postgresql DB 存储 NULL 值需要多少磁盘空间？

BTW, 根据文档：

If the 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小提琴（包括你的测试）。

单 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

Etc.

保留所有表

如果您出于某种原因需要所有四个表，而不是在问题中，请考虑 dba.SE 上一个非常相似的问题的详细解决方案：

强制执行“两张桌子之外”的约束

遗产

...可能是您所描述的另一种选择。如果你能和一些主要限制。相关回答：

在PostgreSQL中创建两种类型的表

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)