MySQLで制約を使ってみる

PostgreSQLでcheckを使ってカラムに入る値を制限する方法があるけど

MySQLで同じような事をやってみる

MySQLの標準で



CREATE TABLE t1 (

  id integer not null primary key,

  c1 tinyint(1) unsigned not null check(c1 between 1 and 2)

)
みたいな事が書けるんだけどcheckの箇所が無視されちゃいます

エラーにはなりません

これを実現してみる

今回はMySQLのコードいじってコンパイルしなおしてとかはしない

まずsql_modeを変更してみる



mysql> set @@sql_mode=TRADITIONAL;

mysql> select @@sql_mode;

@@sql_mode                                                                                                                    |

STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |

1 row in set (0.00 sec

でテーブル作成



CREATE TABLE `t2` (

  `id` int(11) NOT NULL auto_increment,

  `c1` tinyint(1) NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

データ投入



mysql> insert into t2 (c1) values (1000);

ERROR 1264 (22003): Out of range value adjusted for column 'c1' at row 1

となる

次

1,2以外は入れない様にするためTRIGGERのBEFORE UPDATE, INSERTでチェックをする



mysql> delimiter |

mysql> CREATE TRIGGER chk_c1 BEFORE UPDATE ON t2

    -> FOR EACH ROW

    -> BEGIN

    ->     IF NEW.c1 <> 1 AND NEW.c1 <> 2 THEN

    ->         SET NEW.c1 = 999;

    ->     END IF;

    -> END;|

Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TRIGGER chk_c1_ins BEFORE INSERT ON t2

    -> FOR EACH ROW

    -> BEGIN

    ->     IF NEW.c1 <> 1 AND NEW.c1 <> 2 THEN

    ->         SET NEW.c1 = 999;

    ->     END IF;

    -> END;|

Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

これで1,2以外の値の場合999に変換する

tinyintで作成しているためエラーが発生するはず



mysql> insert into t2 (c1) values (-3);

Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;




    

     Level   
     Code 
     Message                                              
    

    

     Warning 
     1264 
     Out of range value adjusted for column 'c1' at row 1 
    


1 row in set (0.00 sec)

あれー？

Query OKって・・・

set @@sql_mode=TRADITIONAL;ってしたのに・・・

まあ、いいや

じゃあプログラム側でwarningをキャッチしてロールバックすればいいか

と言うことで完了