mysql - sql create table，条件是2列不能具有相等的值

This question already has answers here:

CHECK constraint in MySQL is not working
                                
                                    （8个答案）
                                
                        
                        
                            Constraint on a column based on another column
                                
                                    （1个答案）
                                
                        
                                2年前关闭。
            
                    
如何在create table语句中写一个条件说2列不能相等？

CREATE table Example(
    sendId integer,
    recieveId integer,
    **!sendId cannot equal recieveId**

);

最佳答案

使用检查约束：

CREATE table Example(
    sendId integer,
    recieveId integer,
    constraint not_equal check (sendid <> recieveId)
);

由于您的列允许使用空值，因此您可能还需要注意以下几点：

CREATE table Example(
    sendId integer,
    recieveId integer,
    constraint not_equal check (coalesce(sendid,0) <> coalesce(recieveId,0))
);

将NULL视为0也许使用一个永远不会发生的不同值可能更合适。

根据您的DBMS产品，您还可以使用标准运算符is distinct from

constraint not_equal check (sendid is distinct from receiveid)