sql - 在 PostgreSQL 中实现没有触发器的复杂引用

Question

我正在创建一个 PostgreSQL 数据库:国家 - 省 - 市。一个城市必须属于一个国家，也可以属于一个省。一个省份必须属于一个国家。一个城市可以是一个国家的首都:

CREATE TABLE country (
    id serial NOT NULL PRIMARY KEY,
    name varchar(100) NOT NULL
);

CREATE TABLE province (
    id serial NOT NULL PRIMARY KEY,
    name varchar(100) NOT NULL,
    country_id integer NOT NULL,
    CONSTRAINT fk_province_country FOREIGN KEY (country_id) REFERENCES country(id)
);

CREATE TABLE city (
    id serial NOT NULL PRIMARY KEY,
    name varchar(100) NOT NULL,
    province_id integer,
    country_id integer,
    CONSTRAINT ck_city_provinceid_xor_countryid 
        CHECK ((province_id is null and country_id is not null) or 
                (province_id is not null and country_id is null)),
    CONSTRAINT fk_city_province FOREIGN KEY (province_id) REFERENCES province(id),
    CONSTRAINT fk_city_country FOREIGN KEY (country_id) REFERENCES country(id)
);

CREATE TABLE public.capital ( 
    country_id integer NOT NULL,
    city_id integer NOT NULL,
    CONSTRAINT pk_capital PRIMARY KEY (country_id, city_id),
    CONSTRAINT fk_capital_country FOREIGN KEY (country_id) REFERENCES country(id),
    CONSTRAINT fk_capital_city FOREIGN KEY (city_id) REFERENCES city(id)
);

对于某些(但不是全部)国家，我将拥有省份数据，因此一个城市将属于一个省，而该省将属于一个国家。剩下的，我只知道这个城市属于一个国家。

问题 #1:关于我拥有省份数据的国家/地区，我一直在寻找一种解决方案，不允许一个城市属于一个国家，同时又属于另一个国家的一个省。

我更喜欢通过检查约束强制执行，即省或国家(但不是两者)在城市中不为空。看起来是一个巧妙的解决方案。

另一种方法是将省份和国家/地区信息都保存在城市中，并通过触发器强制保持一致性。

问题 #2:我不想承认一座城市是它不属于的国家的首都。在我解决问题 #1 之后，如果没有触发器，这似乎是不可能的，因为无法直接引用城市所属的国家/地区。

也许问题 #1 的替代解决方案更好，它也简化了 future 的查询。

Answer 1

我会彻底简化您的设计:

CREATE TABLE country (
   country_id serial PRIMARY KEY  -- pk is not null automatically
  ,country text NOT NULL          -- just use text
  ,capital int REFERENCES city    -- simplified
);

CREATE TABLE province (           -- never use "id" as name
   province_id serial PRIMARY KEY
  ,province text NOT NULL         -- never use "name" as name
  ,country_id integer NOT NULL REFERENCES country  -- references pk per default
);

CREATE TABLE city (
   city_id serial PRIMARY KEY
  ,city text NOT NULL
  ,province_id integer NOT NULL REFERENCES province,
);

• 由于一个国家只能拥有一个国会大厦，因此不需要 n:m 表。

• 永远不要使用“name”或“id”作为列名。这是一些 ORM 的反模式。一旦连接了几个表(在关系数据库中经常)，您最终会得到多个具有相同非描述性名称的列，从而导致各种问题。

  <

• 只需使用text。 varchar(n) 没有意义。 Avoid problem like this.

• PRIMARY KEY 子句自动生成一列NOT NULL。 (NOT NULL 会保留，即使您稍后删除了 pk 约束。)

最重要的是:

• 在所有 情况下，一个城市仅引用一个省。没有直接引用 country。因此，不匹配是不可能的，磁盘存储更小，您的整个设计很多更简单。查询更简单。

  对于每个 国家/地区，输入一个空字符串作为名称 ('') 的虚拟省份，代表该国家/地区“作为一个整体”。 (可能即使使用相同的 id，你也可以让省份和国家从相同的序列中抽取......)。在触发器中自动执行此操作。不过，此触发器是可选的。

  我选择了一个空字符串而不是 NULL，所以该列仍然可以是 NOT NULL 并且是 (country_id, province) 的唯一索引> 发挥作用。您可以很容易地识别代表全国的这个省份，并在您的申请中酌情处理。

我在多个实例中成功地使用了类似的设计。