Check restriction for mysql

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:

1. trigger-name: credit_tri

2. target-table:student

3. target-field:IDCard

4. 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 $