在继承表上使用触发器来替换外键

Question

我是 PostgreSQL 新手。我有这样的表：

CREATE TABLE Person (
  ID SERIAL PRIMARY KEY,
  Name VARCHAR(32) NOT NULL DEFAULT '',
  Surname VARCHAR(32) NOT NULL DEFAULT '',
  Birthday DATE,
  Gender VARCHAR(8)
);

-- Student table inherits from person
CREATE TABLE Student (
  ID_Student SERIAL PRIMARY KEY,
  MajorDept VARCHAR(32),
) INHERITS(Person);

-- Student table inherits from person
CREATE TABLE Employee (
  ID_Employee SERIAL PRIMARY KEY,
  Position VARCHAR(32),
  Rank VARCHAR(32),
  Salary NUMERIC(12,2)
) INHERITS(Person);

-- Address table references person
CREATE TABLE Address (
  ID_Address SERIAL PRIMARY KEY,
  Person_id INTEGER REFERENCES Person(ID) NOT NULL,
  Email VARCHAR(32) UNIQUE,
  Country VARCHAR(32),
  CityCode INTEGER,
  City VARCHAR(32),
  AddressLine VARCHAR(60),
);

根据这些表，当我想将数据插入 Adress 表时，Postgres 会给出该错误：

错误：表“地址”上的插入或更新违反了外键 约束“address_person_id_fkey”详细信息：密钥 (person_id)=(1) 是 不存在于表“person”中。

我在 Postgres 中了解到

索引（包括唯一约束）和外键约束 仅适用于单个表，不适用于其继承子表。

我的问题是如何使用触发器来解决这个问题？示例代码将非常有用。

向子表插入几行后，我可以使用“SELECT * FROM Person;”查看数据以及。它看起来像：

人员表

1;"Bill";"Smith";"1985-05-10";"male"
2;"Jenny";"Brown";"1986-08-12";"female"
3;"Bob";"Morgan";"1986-06-11";"male"
4;"Katniss";"Everdeen";"1970-08-12";"female"
5;"Peter";"Everdeen";"1968-08-12";"male"

学生表

1;"Bill";"Smith";"1985-05-10";"male";1;"chemistry"
2;"Jenny";"Brown";"1986-08-12";"female";2;"physics"
3;"Bob";"Morgan";"1986-06-11";"male";3;"physics"

员工表

4;"Katniss";"Everdeen";"1970-08-12";"female";1;"Prof";"1";3500.00
5;"Peter";"Everdeen";"1968-08-12";"male";2;"Assist-Prof";"5";1800.00

Answer 1

首先用这样的东西摆脱 FK：

alter table address drop constraint address_person_id_fkey

如果抱怨没有 address_person_id_fkey 约束，那么在 psql 中使用 \d address;找出 FK 的名称。

然后像这样的简单触发器应该可以解决问题：

create or replace function pseudo_fk_for_address() returns trigger as $
begin
    if not exists(select 1 from person where id = new.person_id) then
        raise exception 'No such person: %', new.person_id;
    end if;
    return new;
end;
$ language plpgsql;

并像这样附加它：

create trigger pseudo_fk_for_address_trigger before insert or update on address 
for each row execute procedure pseudo_fk_for_address();

然后，如果您尝试添加 person 中不存在的某人的地址，您将收到这样的错误（包括继承自它的表）：

playpen=> insert into address (person_id, email, country, citycode, city, addressline) values (3, 'ab', 'b', 2, 'c', 'd');
ERROR:  No such person: 3

您希望向 person 添加一个 BEFORE DELETE 触发器以避免悬空引用，该基本结构几乎是相同的。您可能还需要 address.person_id 上的索引来帮助支持 BEFORE DELETE 触发器。

参考文献：

• PL/pgSQL
• PL/pgSQL 触发器过程
• 提高

Answer 2

外键不被继承。如果外键指向表person，那么该表中必须有相同的值。继承的实现在 PostgreSQL 中是有限的，我引用了“Caveats”一章 手册中：

对于这种情况没有好的解决方法。

这包括@Mu 提议的触发器。为了保证引用完整性，您需要的不仅仅是触发器ON INSERT。我不会尝试那样做。如果删除一个人会发生什么？改ID了吗？

我认为根本不使用继承。如果您仍然想要或必须这样做，我会建议对您的数据模型进行一些更改。

• 1) 电子邮件不应该出现在地址表中，它与地址以及与此人的所有内容无关。将其移至表person。错位的原因可能是您想要强制唯一性。根本不使用继承的另一个原因。

• 2) 列 id_student 和 id_employee 是多余的。请使用继承的列 id 作为主键。只需向您的子表添加约束即可：

  约束 Student_pkey 主键（id）
  约束employee_pkey 主键（id）
  

  这还消除了继承树上 id 列中可能重复的两个来源之一。 （另一个是，您仍然可以将 ID 输入到 student 中，这些 ID 存在于 employee 或 person 中。继承系统中的另一个警告。所以，切勿手动插入或更改 id。将其保留为列默认值和顺序。

• 3) “自然”模型将在 < 之间建立 n:m 关系。代码>地址 和人。对于您的模型，我将使用附加表 person_address 来实现它，其中 address_id 引用表 address 和 person_id 仅梦想外键约束（原始问题）。

  按照您的方式，一个地址永远不可能有多个人居住。也许这足以满足您的目的。这样，您也可以将整个地址嵌入到人员表中（并让学生和员工继承它），以完全避免外键问题。