I have a SQL table that can reference another record in the table as its parent but should not reference itself. I have attempted to enforce this with a CHECK constraint but my attempts have failed as the id is an auto-increment column. Is there any other way to ensure that parent_id <> id?

My current attempt, which fails with error 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)
);

CodePudding user response：

Don't put this kind of thing in a constraint. For one thing, you can't do it directly in MySql. You'd have to use a trigger or something.

Instead:

1. write your CRUD code carefully, so it avoids generating incorrect rows. You have to do that anyway.
2. write a little program called "database_consistent" or something. Have it run a bunch of queries looking for any errors like the one you're trying to avoid. Have it send emails or SMSs if it finds problems. Run it often during development and at least daily in production.

CodePudding user response：

Here's a demo of using a trigger to cancel an insert that violates the condition you describe. You must use an AFTER trigger because in a BEFORE trigger the auto-increment value has not yet been generated.

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)

CodePudding user response：

one way to control auto-generated live values is by using triggers to manage new values.