Mysql 如何确保 SQL 记录的列不引用它自己的主键

发布于06月03日

我有一个包含ParentID列的Node表，它引用了它的父级NodeID.我想确保没有 node 可以引用自己(即 node ParentID不能引用自己的NodeID)，因此我try 添加判断约束CHECK(NodeID != ParentID).

但是，我得到了这样的错误:Error Code: 3818. Check constraint 'node_chk_1' cannot refer to an auto-increment column.

我也无法将ParentID添加为Node的外键.

使用MySQL，我如何确保没有NodeID=ParentID的新记录？

推荐答案

使用触发器:

mysql> create table node ( 
  id int auto_increment primary key, 
  parentid int, 
  foreign key (parentid) references node (id)
);

mysql> delimiter ;;

mysql> create trigger no_self_ref after insert on node for each row begin
if NEW.parentid = NEW.id then
  signal sqlstate '45000' message_text = 'no self-referencing hierarchies';
end if;
end;;

mysql> delimiter ;

请注意，它必须是AFTER触发器，因为在BEFORE触发器中尚未生成自动递增id.

演示:

mysql> insert into node values (1, null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into node values (2, 2);
ERROR 1644 (45000): no self-referencing hierarchies

mysql> insert into node values (2, 1);
Query OK, 1 row affected (0.01 sec)

如果您想要防止用户更新该行并将parentid设置为与同一行中的id相同的值，则还需要一个类似的AFTER UPDATE触发器.

另一种解决方案是将主键设置为非自动递增.您必须在INSERT语句中指定每个id值，而不是让它们自动递增.但这将允许您使用判断约束.