SQL constraint to make 2 columns not equal to each other

Question

I have a table that has two columns to store id from another table. Column1 gets id from ABC table and Column2 also gets id from that table but letter is called parent ID, so with this information I know who is parent of who.

Now I want to create a constraint not to ever let both columns to get same id. The following did not work:

ALTER TABLE id_parent_table
ADD CHECK (parent_id != main_id)

This is still allowing to insert two identical numbers.

The syntax is correct - can you elaborate a bit on how it didn't work? — Mureinik, Apr 17 '14 at 10:29
@Mureinik After that I can still insert the same id to main_id and parent_id — Jamol, Apr 17 '14 at 10:33
Here you go this will help: http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working — CodeBird, Apr 17 '14 at 10:40

score 2 · Answer 1 · answered Dec 02 '20 at 21:43

This is now supported as of MySQL 8.0.16.

See https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

mysql> create table id_parent_table (
    -> main_id bigint unsigned not null,
    -> parent_id bigint unsigned not null,
    -> constraint columns_cannot_equal check (main_id <> parent_id)
    -> );
Query OK, 0 rows affected (0.38 sec)

mysql> insert into id_parent_table (main_id, parent_id) values (1, 1);
ERROR 3819 (HY000): Check constraint 'columns_cannot_equal' is violated.

score 0 · Answer 2 · edited Jan 11 '16 at 12:20

0

Apparently, MySQL does not support check constraints. To quote the online reference:

The CHECK clause is parsed but ignored by all storage engines.

You could, alternatively, use a trigger to fail such an insert or update:

EDIT: MySQL doesn't support a single trigger on two events, so you'd have to have two different triggers:

delimiter //
CREATE TRIGGER id_parent_table_check_insert_trg
BEFORE INSERT ON id_parent_table
FOR EACH ROW
BEGIN
    DECLARE msg varchar(255);
    IF new.parent_id = new.main_id THEN
        SET msg = 'parent_id and main_id should be different';
        SIGNAL SQLSTATE '45000' SET message_text = msg;
    END IF;
END
//

CREATE TRIGGER id_parent_table_check_update_trg
BEFORE UPDATE ON id_parent_table
FOR EACH ROW
BEGIN
    DECLARE msg varchar(255);
    IF new.parent_id = new.main_id THEN
        SET msg = 'parent_id and main_id should be different';
        SIGNAL SQLSTATE '45000' SET message_text = msg;
    END IF;
END
//

edited Jan 11 '16 at 12:20

99 Problems - Syntax ain't one

555
7
17

answered Apr 17 '14 at 10:43

Mureinik

297,002
52
306
350

You are right. It seems we cannot use check constraint in MySQL. Nevertheless your code to create a trigger is not working too. It has syntax errors, and it seems MySQL does not support BEFORE INSERT OR UPDATE too – Jamol Apr 17 '14 at 11:27
@jCloud forgot MySQL does support triggers on multiple events. See edit. – Mureinik Apr 17 '14 at 11:29
1

OK. Now please edit your answer to correct syntax errors like ENF ID; Please run it on your machine first. So then I can accept your answer as Correct one. Thanks – Jamol Apr 17 '14 at 11:36

SQL constraint to make 2 columns not equal to each other

2 Answers2

Linked