Check restriction for mysql

MySQL触发器实现Check约束

最新推荐文章于 2025-07-09 22:09:54 发布

转载最新推荐文章于 2025-07-09 22:09:54 发布 · 87 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/22Kon/p/6713688.html

本文介绍如何使用MySQL触发器来模拟Oracle中的Check约束功能，通过一个具体示例——限制学生表中ID卡字段长度不超过18位——展示了触发器的具体实现过程。

Check restriction for mysql

Mysql does not suppot for check restriction but oracle does. Here using trigger we can achive the same restrction as check does.

Scenario:

Here is a IDcard field in table student with a restriction that the length of IDCrad must less than 18.

4 parameters we can use:

trigger-name: credit_tri
target-table:student
target-field:IDCard
error-msg:"length is above 18,cannot insert"

mysql> delimiter $
mysql> create trigger credit_tri before insert 
    -> on student for each row
    -> begin
    ->     declare msg varchar(200); 
    ->     if (new.IDCard > 18) then  
    ->         set msg = "length is above 18. Cannot insert.";    
    ->             signal sqlstate 'HY000' SET message_text = msg;    
    ->     end if;
    -> end $