添加对值的约束，Mysql [英] Add constraint on values , Mysql

问题描述

我的桌子

CREATE TABLE STUDENT ( 
      BCN INT not null,
      Stname varchar(50) not null,
      Sex char(1) not null ,
      primary key (BCN));

我想让Sex属性只接受两个值'F'或'M'

I want to make Sex attribute just accept two values 'F' or 'M'

我尝试了'check'子句:

I've try 'check' clause :

 Sex char(1) not null check (Sex = 'F' or Sex ='M' ) 

但不起作用，性别"列仍接受其他值，例如('B')！

but doesn't work, the Sex column still accept other values like ('B') !

并且我尝试创建触发器:

and I've try to create trigger :

DELIMITER $$ CREATE TRIGGER SexCheck BEFORE INSERT ON student
FOR EACH ROW
BEGIN
    IF Sex <> 'M' and Sex <> 'F' THEN
    SIGNAL SQLSTATE '10000'
        SET MESSAGE_TEXT = 'check constraint on Student.Sex failed';
    END IF;
END$$   
DELIMITER ;

它不会产生任何语法错误，但性别"列甚至不接受"M"或"F"的任何值！它显示了我已设置的消息.

It doesn't produce any syntax error , but the Sex column doesn't accept any values even 'M' or 'F'! it shows the message that I've set .

我该如何解决?

-我正在使用mysql workbench 6.3.

-I'm using mysql workbench 6.3.

预先感谢

推荐答案

来自 CREATE TABLE :

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

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

第二:

CREATE TRIGGER SexCheck BEFORE INSERT ON  STUDENT
FOR EACH ROW
BEGIN
    IF New.Sex NOT IN('F', 'M') THEN
    SIGNAL SQLSTATE '10000'
        SET MESSAGE_TEXT = 'check constraint on Student.Sex failed';
    END IF;
END;


INSERT INTO STUDENT(Sex) VALUES ('B');
-- check constraint on Student.Sex failed

SqlFiddleDemo

这篇关于添加对值的约束，Mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！