外键从一列到两列不同的表按条件。

I have tree tables MySQL:

我有树表MySQL:

articles table contains:

文章表包含:

id int
title varchar(255)
...

news table contains:

新闻表包含:

id int
title varchar(255)
...

comments table contains:

评论表包含:

id int
content text
type tinyint(1)  //this column holds 0 if this comment for news and holds 1 for article
fid  int         // this column holds id of article or news

how can I make foreign key from comments table to articles and news. I mean how can implement this in MySQL query:

如何将外键从评论表变成文章和新闻。我的意思是如何在MySQL查询中实现这一点:

if type=0 then
 FOREIGN KEY (fid) REFERENCES news(id)

if type=1 then
 FOREIGN KEY (fid) REFERENCES articles(id)

2 个解决方案

#1

To have proper PK-FK relationships I'd suggest to have a superset table (lets call it posts). In that case your schema might look like

为了建立正确的PK-FK关系，我建议使用一个超集表(我们称它为posts)。在这种情况下，您的模式可能是这样的

CREATE TABLE posts 
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  type TINYINT(1)
);

CREATE TABLE articles
(
  id INT NOT NULL,
  title VARCHAR (255),
  article_property VARCHAR(128),
  -- other article specific attributes
  CONSTRAINT FOREIGN KEY (id) REFERENCES posts (id)
);

CREATE TABLE news
(
  id INT NOT NULL,
  title VARCHAR (255),
  reporter VARCHAR(128),
  -- other news specific attributes
  CONSTRAINT FOREIGN KEY (id) REFERENCES posts (id)
);

CREATE TABLE comments
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  post_id INT NOT NULL,
  content TEXT,
  CONSTRAINT FOREIGN KEY (post_id) REFERENCES posts (id)  
);

To populate ids when inserting new articles and news you can make use of triggers

要在插入新文章和新闻时填充id，可以使用触发器

DELIMITER $$
CREATE TRIGGER tg_article_insert 
BEFORE INSERT ON articles
FOR EACH ROW
BEGIN
  INSERT INTO posts (type) VALUES (1);
  SET NEW.id = LAST_INSERT_ID();
END$$

CREATE TRIGGER tg_news_insert 
BEFORE INSERT ON news
FOR EACH ROW
BEGIN
  INSERT INTO posts (type) VALUES (0);
  SET NEW.id = LAST_INSERT_ID();
END$$
DELIMITER ;

Here is SQLFiddle demo.

这是SQLFiddle演示。

#2

You can't. You can read about foreign key constraints here, and you will note that only one table is allowed.

你不能。您可以在这里阅读有关外键约束的内容，您将注意到只允许有一个表。

One work-around is to have separate columns for the separate ids:

一种方法是为不同的id设置不同的列:

id int
content text
type tinyint(1)   //this column holds 0 if this comment for news and holds 1 for article
articleid  int
newsid int
. . . 
foreign key (articleid) references articles(id)
foreign key (newsid) references news(id)

In fact, you can dispense with the type and add in a constraint (implemented by a trigger) that only one id can be populated at any given time.

事实上，您可以省去类型，并添加一个约束(由触发器实现)，在任何给定时间只能填充一个id。

#1