sqlite 如何防止孤立的多态记录？

qmelpv7a 于 2022-11-24 发布在 SQLite

关注(0)|答案(2)|浏览(139)

我有一个多态结构的数据库：一个“基”类型表和两个“派生”类型：

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记录，视图将触发违规。“显而易见”的方法是在视图上设置约束，但这是不允许的。因此，这必须是某种技巧，可能涉及视图上的索引。这真的是强制无孤立的最佳（唯一？）方法吗？如果是，工作示例是什么？
还有其他方法吗？我可以去掉ContactMethod表，并在其他两个表上复制共享列，但我不想这么做。我主要对MySQL和SQLite中可用的功能感到好奇，但任何SQL引擎中的解决方案都将有所帮助。

sqlite

来源：https://stackoverflow.com/questions/74548748/how-to-prevent-orphaned-polymorphic-records

2条答案

按热度按时间

z18hc3ub1#

最简单的解决方案是使用单表继承。因此，两个联系人方法都是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帐户），则必须向该表添加另一个外键。

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)
)

赞(0）回复(0）举报 2022-11-24

0x6upsns2#

在插入电话号码或电子邮件地址之前，新插入的ContactMethod始终是孤立的。因此，您无法在插入时测试条件。
相反，您可以使用除了基本信息之外还具有可选电话号码和可选电子邮件参数的存储过程来插入联系人信息。仅当这两个参数中至少有一个具有非空值时，才会插入基本记录。
然后，在删除电话号码或电子邮件地址时创建一个删除触发器，以便在不再存在相关记录时删除ContactMethod记录，或者引发异常，如Alter a Delete Trigger to Check a Column Value中所示

赞(0）回复(0）举报 2022-11-24

我来回答

sqlite 如何防止孤立的多态记录？

2条答案

相关问题

热门标签

最新问答