Sql Constraint To Make 2 Clumns Not Equal To Each Other

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

Solution 1:

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>createtable id_parent_table (
    -> main_id bigint unsigned notnull,
    -> parent_id bigint unsigned notnull,
    ->constraint columns_cannot_equal check (main_id <> parent_id)
    -> );
Query OK, 0rows affected (0.38 sec)

mysql>insertinto id_parent_table (main_id, parent_id) values (1, 1);
ERROR 3819 (HY000): Checkconstraint'columns_cannot_equal'is violated.

Solution 2:

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 //CREATETRIGGER id_parent_table_check_insert_trg
BEFORE INSERTON id_parent_table
FOREACHROWBEGINDECLARE msg varchar(255);
    IF new.parent_id = new.main_id THENSET msg ='parent_id and main_id should be different';
        SIGNAL SQLSTATE'45000'SET message_text = msg;
    END IF;
END//CREATETRIGGER id_parent_table_check_update_trg
BEFORE UPDATEON id_parent_table
FOREACHROWBEGINDECLARE msg varchar(255);
    IF new.parent_id = new.main_id THENSET msg ='parent_id and main_id should be different';
        SIGNAL SQLSTATE'45000'SET message_text = msg;
    END IF;
END//