检查约束不适用于mysql

问题描述：

检查约束不起作用

CREATE TABLE IF NOT EXISTS supervisor (
sup_id INT(3) NOT NULL,
sup_name VARCHAR(30) NOT NULL,
gen VARCHAR(1) NOT NULL CHECK (gen='M' or gen='F'),
dep_id INT(4),

PRIMARY KEY (sup_id),
INDEX (dep_id),

FOREIGN KEY (dep_id)
    REFERENCES department(dep_id)
    ON UPDATE CASCADE ON DELETE RESTRICT    
);

我也尝试过:

 CONSTRAINT chk_supervisor_gen CHECK ('M' or 'F')

这些都没有阻止输入此信息

neither of these stopped this information being entered

INSERT
INTO supervisor (sup_id, sup_name, gen, dep_id)
VALUES  
       (1, 'hello', 'G', 1);

答

MySQL不强制执行检查约束.

MySQL doesn't enforce check constraints.

这是与SQL标准的有据可查的偏差. (尽管这是始料未及的.)

This is a well documented deviation from the SQL standard. (Though it unexpected by the uninitiated.)

如果您需要MySQL数据库来实施检查约束"，则必须将实施编码为BEFORE INSERT和BEFORE UPDATE触发器.

If you need the MySQL database to enforce a "check constraint", the enforcement has to be coded into a BEFORE INSERT and a BEFORE UPDATE trigger.

此注释:

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

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

嵌入在CREATE TABLE语法下的《 MySQL参考手册》中.

is buried in the MySQL Reference Manual, under the CREATE TABLE syntax.

参考: https://dev.mysql.com/doc/refman/5.5/en/create-table.html

关于枚举的警告

ENUM不会限制插入无效"值；无效的值将转换为长度为零的字符串，会发出警告，但这不是错误.

An ENUM does not restrict "invalid" values from being inserted; an invalid value is translated into a zero length string, a warning is issued, but it's not an error.

演示:

CREATE TABLE foo (gen ENUM('M','F'))

INSERT INTO foo (gen) VALUES ('x')

-- Warning Code : 1265
-- Data truncated for column 'gen' at row 1

SELECT gen, CHAR_LENGTH(gen) FROM foo;

-- gen  CHAR_LENGTH(gen)  
-- ---  ----------------
--                     0

检查约束不适用于mysql

相关推荐