How can I make sure a SQL record's column doesn't refer to its own primary key?

Question

I have a Node table with a ParentID column, which refers to a NodeID that is its parent. I want to make sure no Node can refers to itself (i.e. a Node's ParentID cannot its own NodeID), so I tried adding a check constraint CHECK(NodeID != ParentID).

However, I got this error: Error Code: 3818. Check constraint 'node_chk_1' cannot refer to an auto-increment column.

I also couldn't add the ParentID as a foreign key of Node.

Using MySQL, How can I make sure that there are no new records where NodeID = ParentID?

Answer 1

Use a trigger:

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 ;

Note that it must be an AFTER trigger, because the auto-increment id has not yet been generated in a BEFORE trigger.

Demo:

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)

You will also need a similar AFTER UPDATE trigger, if you want to prevent users from updating the row and setting the parentid to the same value as the id in the same row.

---

An alternative solution would be to make the primary key non-auto-increment. You will have to specify every id value in your INSERT statements, instead of letting them be auto-incremented. But this will allow you to use a CHECK constraint.