Question 20934 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL CHECK constraint to ensure record does not refer to itself with autoincrement id?

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)
);
Tags:

3

Answers


  1. 0

    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.
    Login or Signup to reply.
  2. 0

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

    For example, create instead of insert trigger to control newly generated ID. In triggers, you can make decisions based on the new value.

    Login or Signup to reply.
  3. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search