非空检查不起作用。

Question

我正在制作一张像下面这样的桌子。

create table modifications (
    id bigint(20) AUTO_INCREMENT not null primary key,
    type varchar(100),
    user_id bigint(20) null,
    vulnerability_id bigint(20) null
)
alter table modifications
    add constraint fk_user_id foreign key (user_id) references app_user(id)
alter table modifications
    add constraint fk_vulnerability_id foreign key (vulnerability_id) references vulnerabilities(id)
alter table modifications
    add constraint ck_OneIsNotNull check (user_id is not null or vulnerability_id is not null)
alter table modifications
    add constraint ck_OneIsNull check (user_id is null or vulnerability_id is null)

我的目标是列之一应该为空，另一列不应该为空。但是，当我同时插入null或null时，它是接受的，没有抛出错误。下面是我使用的insert查询。

insert into modifications (type,user_id,vulnerability_id) values('vulnerability',16,65)
insert into modifications (type) values('vulnerability')

我哪里出错了？

Answer 1

MySQL不强制执行检查约束。

这在MySQL参考手册中有记录。

您可以创建一个“前插入”和“更新前”触发器，以强制执行这样的约束。

例如：

 DELIMITER $$

 CREATE TRIGGER modifications_bi
 BEFORE INSERT ON modifications
 FOR EACH ROW
 BEGIN
    IF ( NEW.user_id IS NULL AND NEW.vulnerability_id IS NULL )
    OR ( NEW.user_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL ) THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error: one must be null and one must be non-null';
    END IF;
END$$

DELIMITER ;

如果在5.5之前运行版本，则不支持信号语法。若要抛出异常，则需要运行导致异常的SQL语句。