如何防止孤儿多态记录?

Question

我有一个多态结构的数据库:一个"base"类型表和两个"派生"类型:

CREATE TABLE ContactMethod(
id integer PRIMARY KEY
person_id integer
priority integer
allow_solicitation boolean 
FOREIGN KEY(person_id) REFERENCES People(id)
)
CREATE TABLE PhoneNumbers(
contact_method_id integer PRIMARY KEY
phone_number varchar
FOREIGN KEY(contact_method_id) REFERENCES ContactMethod(id)
)
CREATE TABLE EmailAddresses(
contact_method_id integer PRIMARY KEY
email_address varchar
FOREIGN KEY(contact_method_id) REFERENCES ContactMethod(id)
)

我想防止孤儿ContactMethod记录的存在，即ContactMethod记录既没有相应的PhoneNumber记录，也没有相应的EmailAddress记录。我已经看到了确保独占性的技术(防止ContactMethod记录同时具有相关的PhoneNumber和EmailAddress)，但没有看到防止孤儿的技术。

一个想法是使用CHECK约束来执行执行查询的自定义函数。但是，通过CHECK约束中的函数执行查询是一个坏主意。

另一个想法是，如果添加了孤立的ContactMethod记录，将触发冲突的视图。"obvious"一种方法是在视图上加一个约束，但这是不允许的。这需要一些技巧，可能涉及到视图上的索引。这真的是最好的(唯一的?)强制没有孤儿的方法吗?如果是这样，有什么可行的例子?

还有其他方法吗?我可以删除ContactMethod表，并在其他两个表上复制共享列，但我不想这样做。我主要对MySQL和SQLite的可用功能感到好奇，但任何SQL引擎的解决方案都会有所帮助。

Answer 1

最简单的解决方案是使用单表继承。因此，这两个联系人方法都是ContactMethod表中的可选(即可空)字段，但是您可以添加CHECK约束以确保其中至少有一个具有非空值。

CREATE TABLE ContactMethod(
id integer PRIMARY KEY
person_id integer
priority integer
allow_solicitation boolean,
phone_number varchar DEFAULT NULL
email_address varchar DEFAULT NULL 
FOREIGN KEY(person_id) REFERENCES People(id)
CHECK (COALESCE(phone_number, email_address) IS NOT NULL)
)

另一个支持多态关联的解决方案是反转外键的方向。使ContactMethod为每种关联方法类型都有一个可空的外键。使用CHECK来确保至少有一个具有非空值。这是因为你不允许在ContactMethod中每行有多个电子邮件或电话。这意味着如果你添加了一个不同类型的联系人(例如Signal account)，那么你必须在这个表中添加另一个外键。

CREATE TABLE ContactMethod(
id integer PRIMARY KEY
person_id integer
priority integer
allow_solicitation boolean,
phone_number_id integer DEFAULT NULL
email_address_id integer DEFAULT NULL 
FOREIGN KEY(person_id) REFERENCES People(id)
FOREIGN KEY(phone_number_id) REFERENCES PhoneNumbers(id)
FOREIGN KEY(email_address_id) REFERENCES EmailAddresses(id)
CHECK (COALESCE(phone_number_id, email_address_id) IS NOT NULL)
)

Answer 2

新插入的ContactMethod将始终是孤儿，直到您插入电话号码或电子邮件地址。因此，您不能测试insert的条件。

相反，您可以使用存储过程插入联系人信息，该存储过程除了基本信息之外，还具有可选的电话号码和可选的电子邮件参数。只有当两个记录中至少有一个具有非空值时，才会插入基本记录。

然后创建一个删除触发器，当一个电话号码或电子邮件地址被删除时，删除ContactMethod记录，当不存在相关记录时，或者引发一个异常，如Alter a delete trigger to Check a Column Value

所示