有没有这样一个反向的外键？ [英] Is there such a thing as a reverse foreign key?

问题描述

在MySQL中，是否有一种方法来指定 a.column 不能存在于 b.column - a反向外键？

换句话说：

 ＃不会返回任何行
 SELECT * FROM 
 INNER JOIN b ON a.column = b.column; 
 
＃会失败
插入
 SELECT * FROM b; 
 
＃不会插入任何行
 INSERT IGNORE IN TO 
 SELECT * FROM b; 
  

解决方案

没有这样的事情。

您需要在触发器中执行此操作：

  DELIMITER $$ 
 
 CREATE TRIGGER bi_a_each插入之前为每行
 BEGIN 
 DECLARE forbidden_​​key INTEGER; 
 SELECT id INTO forbidden_​​key FROM b WHERE b.id = NEW.acolumn LIMIT 1; 
 IF forbidden_​​key不为空然后
 SELECT * FROM error_insertion_of_this_value_is_not_allowed; 
 END IF; 
 END $$ 
 
 DELIMITER; 
  

In MySQL, is there a way to specify that a.column cannot exist in b.column - a reverse foreign key?

In other words:

# Would not return any rows ever
SELECT * FROM a
INNER JOIN b ON a.column = b.column;

# Would fail
INSERT INTO a
SELECT * FROM b;

# Would not insert any rows
INSERT IGNORE INTO a
SELECT * FROM b;

解决方案

No there is no such thing.

You would need to do that in a trigger:

DELIMITER $$

CREATE TRIGGER bi_a_each BEFORE INSERT ON a FOR EACH ROW
BEGIN
  DECLARE forbidden_key INTEGER;
  SELECT id INTO forbidden_key FROM b WHERE b.id = NEW.acolumn LIMIT 1;
  IF forbidden_key IS NOT NULL THEN 
    SELECT * FROM error_insertion_of_this_value_is_not_allowed;
  END IF;
END $$

DELIMITER ;

这篇关于有没有这样一个反向的外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！