CODEWITHSUNDEEP
mysqlsqlconstraintsmultiple-columns
Jamol
Jamol

Reputation: 3918

SQL constraint to make 2 columns 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 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.

Upvotes: 2

Views: 8098

Answers (2)

RandomStranger
RandomStranger

Reputation: 31

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.

Upvotes: 3

Mureinik
Mureinik

Reputation: 312219

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
//

Upvotes: 0

Related Questions