SQL约束，使两个笨拙彼此不相等 [英] SQL constraint to make 2 clumns not equal to each other

问题描述

我有一个表，该表有两列用于存储另一个表的ID. Column1从ABC表中获取ID，而Column2也从该表中获取ID，但字母称为父ID，因此借助此信息，我知道谁是谁的父.

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.

现在，我想创建一个约束，使两个列都不能获得相同的ID. 以下内容无效:

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.

推荐答案

显然，MySQL不支持检查约束.引用在线参考:

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

CHECK子句被解析，但被所有存储引擎忽略.

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

或者，您可以使用触发器使插入或更新失败:

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

MySQL不支持在两个事件上使用单个触发器，因此您必须具有两个不同的触发器:

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

这篇关于SQL约束，使两个笨拙彼此不相等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！