MySQL - 条件外键约束

2021-11-20 00:00:00 sql foreign-keys mysql database-design polymorphic-associations

我的应用中有以下 comments 表:

I have following comments table in my app:

comments
--------
id           INT
foreign_id   INT
model        TEXT
comment_text TEXT
...

这个表的想法是为我的应用程序的各个部分存储评论——它可以存储博客文章的评论，即:

the idea of this table is to store comments for various parts of my app - it can store comments for blog post i.e.:

1|34|blogpost|lorem ipsum...

用户图片:

2|12|picture|lorem ipsum...

等等.

现在，有没有办法强制对此类数据进行 FOREIGN KEY 约束?

now, is there a way to force FOREIGN KEY constraint on such data?

即评论表中的类似内容:

i.e. something like this in comments table:

FOREIGN KEY (`foreign_id`) REFERENCES blogposts (`id`)
-- but only when model='blogpost'

推荐答案

您正在尝试进行一种称为多态关联的设计.也就是说，外键可以引用几个相关表中的任何一个.

You're attempting to do a design that is called Polymorphic Associations. That is, the foreign key may reference rows in any of several related tables.

但是一个外键约束必须只引用一个表.您不能根据 Comments 表的另一列中的值声明引用不同表的外键.这将违反关系数据库设计的几条规则.

But a foreign key constraint must reference exactly one table. You can't declare a foreign key that references different tables depending on the value in another column of your Comments table. This would violate several rules of relational database design.

更好的解决方案是制作一种由注释引用的超级表".

A better solution is to make a sort of "supertable" that is referenced by the comments. 

CREATE TABLE Commentable (
  id SERIAL PRIMARY KEY
);

CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  foreign_id INT NOT NULL,
  ...
  FOREIGN KEY (foreign_id) REFERENCES Commentable(id)
);

您的每种内容类型都将被视为此超级表的子类型.这类似于面向对象的接口概念.

Each of your content types would be considered a subtype of this supertable. This is analogous to the object-oriented concept of an interface.

CREATE TABLE BlogPosts (
  blogpost_id INT PRIMARY KEY, -- notice this is not auto-generated
  ...
  FOREIGN KEY (blogpost_id) REFERENCES Commentable(id)
);

CREATE TABLE UserPictures (
  userpicture_id INT PRIMARY KEY, -- notice this is not auto-generated
  ...
  FOREIGN KEY (userpicture_id) REFERENCES Commentable(id)
);

在您将一行插入到 BlogPosts 或 UserPictures 之前，您必须向 Commentable 插入一个新行以生成新的伪键 id.然后，您可以在将内容插入到相应的子类型表时使用生成的 id.

Before you can insert a row into BlogPosts or UserPictures, you must insert a new row to Commentable to generate a new pseudokey id. Then you can use that generated id as you insert the content to the respective subtype table.

完成所有这些后，您就可以依赖参照完整性约束了.

Once you do all that, you can rely on referential integrity constraints.

相关文章