MySQL CHECK 约束以确保记录不使用自动增量 ID 引用自身

发布于02月05日

我有一个SQL表，它可以引用表中的另一个记录作为其parent，但不应该引用它自己.我曾try 使用CHECK约束来强制执行此操作，但try 失败了，因为id是一个自动递增列.有没有其他方法可以确保parent_id <> id？

我当前的try 失败，错误为Check constraint 'not_own_parent' cannot refer to an auto-increment column. (errno 3818):

CREATE TABLE `content` (
    `id` serial PRIMARY KEY NOT NULL,
    `item_id` int NOT NULL,
    `nested_item_id` int,
    `block_id` int,
    `order` int NOT NULL,
        CONSTRAINT not_own_parent CHECK (nested_item_id <> id)
);

推荐答案

下面是使用触发器取消违反您所描述的条件的插入的演示.必须使用AFTER触发器，因为在BEFORE触发器中尚未生成自动增量值.

mysql> delimiter ;;
mysql> create trigger t after insert on content
    -> for each row begin
    -> if NEW.nested_item_id = NEW.id then
    -> signal sqlstate '45000' set message_text = 'content cannot reference itself';
    -> end if;
    -> end;;
mysql> delimiter ;

mysql> insert into content set item_id = 1, nested_item_id = 1, `order` = 1;
ERROR 1644 (45000): content cannot reference itself

mysql> insert into content set item_id = 1, nested_item_id = 2, `order` = 1;
Query OK, 1 row affected (0.01 sec)