MySQL - 有条件的外键限制 [英] MySQL - Conditional Foreign Key Constraints

问题描述

我的应用中有以下评论表：

 评论
 ------- -  
 id INT 
 foreign_id INT 
模型TEXT 
 comment_text TEXT 
 ... 
  

这个表的想法是存储我应用程序各个部分的注释 - 它可以存储博客文章的注释，即：

  1 | 34 | blogpost | lorem ipsum ... 
  

用户图片：

  2 | 12 |图片| lorem ipsum ... 
  / pre> 
 
 
等等。
 
 
 
现在，有没有办法强制FOREIGN KEY这样的数据约束？
 
 
 
 ie在评论表中的这样一个例子：
  FOREIGN KEY（`foreign_id`）参考blogposts（`id`）
 / /但只有当model ='blogpost'
  
 
 
解决方案
尝试进行称为多态关联的设计。也就是说，外键可以引用几个相关表格中的任何一个。
 
 
 
但外键约束必须仅引用一个表。您不能根据评论表的另一列中的值，声明引用不同表格的外键。这将违反关系数据库设计的几个规则。
 
 
 
一个更好的解决方案是做出一种由评论引用的supertable。 
  CREATE TABLE Commentable（
 id SERIAL PRIMARY KEY 
）; 
 
 CREATE TABLE注释（
 comment_id SERIAL PRIMARY KEY，
 foreign_id INT NOT NULL，
 ... 
 FOREIGN KEY（foreign_id）REFERENCES Commentable（id ）
）; 
  
您的每个内容类型都将被视为此超级类型。这类似于接口的面向对象的概念。
  CREATE TABLE BlogPosts（
 blogpost_id INT PRIMARY KEY， - 注意这不是自动生成
 ... 
 FOREIGN KEY（blogpost_id）参考Commentable（id）
）; 
 
 CREATE TABLE UserPictures（
 userpicture_id INT PRIMARY KEY， - 注意这不是自动生成
 ... 
 FOREIGN KEY（userpicture_id）REFERENCES Commentable（id ）
）; 
  
可以在 BlogPosts 中插入一行或 UserPictures ，则必须在 Commentable 中插入一个新行以生成新的伪随机ID。然后，您可以在将内容插入到相应的子类型表中时使用该生成的id。
 
 
 
一旦你这样做，你可以依赖参照完整性约束。 > 
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...
user picture:
2|12|picture|lorem ipsum...
and so on.

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.

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

                        
这篇关于MySQL  - 有条件的外键限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！