Mysql Constraint Check Workarounds(MYSQL不支持约束的处理)

Mysql Constraint Check Workarounds MYSQL不支持约束的处理

http://forums.mysql.com/read.php?136,152474,240479#msg-240479

As the MySQL Reference Manual says:
"The CHECK clause is parsed but ignored by all storage engines."
MySQL does not support CHECK constraints.
It’s a bug of MySQL. (http://bugs.mysql.com/bug.php?id=25660 )

 

The way I do a constraint check is with TRIGGER (see : http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html ).
You can use a trigger to route the update to an error condition like duplicate key.

Basically, for your table, you would say:

DELIMITER $$
CREATE TRIGGER trig_gvars_gfirst BEFORE UPDATE ON gvars
FOR EACH ROW BEGIN
IF NEW.name NOT LIKE 'g%' THEN
SET NEW.name = 1 / 0;
END IF;
END $$

DELIMITER ;

Basically, the line "SET NEW.name = 1 / 0;" will occur and force an error any time the constraint is not satisfied. That seems to be the best way to cause the insertions not to happen. Quite dirty -- but it beats just getting ignored by the database engine. This also doesn't work if you're updating through FOREIGN KEY actions, such as CASCADE or UPDATE, as TRIGGERs are not called for these actions. So watch out for that.

Another solution would be to restrict insertion of data into tables you would like to constrain through user-defined functions and procedures. This requires enumerating every possible action scheme you might want to take, so it is generally not the best option.

 

 

不能使用ENUM 类型来代替Constraint Check， MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将会使 MySQL 在这个字段中插入一个空字符串， 而不是插入失败.