SQLにはチェック制約という仕組みがあり、この機能が設定されていると、データベースに登録されるデータが条件に合わない場合は登録させません。そして、この機能をうまく活用すれば、システムを誤動作させるデータの登録を防ぐことが可能です。今回は、このようなチェック制約について解説します。
SQLのチェック制約の基本
SQLには、INSERT句でテーブルにデータを格納する際、条件を設定して、それに合うデータのみ登録できるようにする、チェック制約という機能があります。まずは、チェック制約の基本について解説します。
そもそもチェック制約とは何か
一般的なWebシステムでは、ユーザーに登録してもらったデータをプログラム側でチェックします。そして、条件に合致していればそのデータを格納するSQLを作成し、データベースに登録されます。しかし、意図しないデータが入力されたり、一括して登録したりすると、チェックが働かず、Webシステムで処理できないデータが作られてしまうこともあります。
チェック制約は、このような事態を防ぐために、データベース側で登録されるデータをチェックし、条件に合致していなければ、登録できないようにする機能です。なお、このチェック制約は、テーブル作成時に設定することから、事前にどのようなデータを扱うかが解らないと設定できないので注意してください。
チェック制約の登録方法
チェック制約は、CREATE TABLEでテーブルを作成する際に、列の定義にCHECK句を使って設定します。なお、チェック制約は、まるでIF文のようにORやANDで条件を指定することで、１つの列に複数の条件を設定することも可能です。
チェック制約の構文
CHECK( 条件式 )
チェック制約を使った例
CREATE TABLE sample1
(
id INT PRIMARY KEY,
age INT,
gender CHAR(1),
CONSTRAINT childcheck CHECK(age >= 6 AMD age <= 15 ),
CONSTRAINT gendercheck CHECK(gender IN('M', 'F'))
) ;
チェック制約の効果
チェック制約を設定したテーブルに対して、その条件に合わないデータを登録しようとするとエラーになり、そのデータは登録されません。ただし、エラーメッセージはデータベース管理ソフトによって違います。事前にクライアントツールなどでチェックしておきましょう。
Oracleでチェック制約が効いた場合の例 行1でエラーが発生しました。: ORA-02290: チェック制約(XXXX.CHILDCHECK)に違反しました MySQL8.0.16以降でチェック制約が効いた場合の例 ERROR 3819 (HY000): Check constraint 'childcheck' is violated. mariadbでチェック制約が効いた場合の例 ERROR 4025 (23000)]: CONSTRAINT 'childcheck' failed for 'test'.'sample1'
ユニーク制約とチェック制約の違い
SQLには、チェック制約と似た機能として、ユニーク制約があります。次から、チェック制約とユニーク制約の違いについて解説します。
ユニーク制約の基本
ユニーク制約は、チェック制約と同じく、列のデータを登録したり、格納されたデータを修正する際、そのデータに制約を加える機能です。そして、テーブル作成時にユニーク制約を設定しておくと、以前登録したデータと重複しているかチェックし、もし重複していればデータが格納されません。
なお、ユニーク制約を設定するには、列を定義する際、UNIQUE句を追加します。
ユニーク制約を使った例 CREATE TABLE sample2 ( id INT UNIQUE, name TEXT );
この例では、id列をユニーク制約として設定しています。そのためこの例では、idには重複した数字を登録できません。
ユニーク制約の実行例
次に、代表的なデータベース管理ソフトで出力される、ユニーク制約でデータが登録できなかった場合のメッセージの例を紹介します。
Oracleでユニーク制約が効いた場合の例 ORA-00001: 一意制約(XXXXX.SYS_CXXXXX)に反しています
MySQLでユニーク制約が効いた場合の例 ERROR 1062 (23000): Duplicate entry '1' for key 'id'
チェック制約の宣言の簡略化
先ほど、チェック制約の設定で、CONSTRAINT句を使った例を紹介しました。このCONSTRAINT句は、ほとんどのデータベース管理ソフトでも使えます。ただし、チェック制約の設定は、このCONSTRAINT句を使った方法だけではありません。データベース管理ソフトによっては、より簡略化した宣言も可能です。次から、そのような簡略化した宣言の書き方について紹介します。
OracleはCONSTRAINT句を省略できる
先ほど、CONSTRAINT句を使ったチェック制約の例を紹介しましたが、Oracle Databaseでは、列の定義に続けてCHECK文で入力できる値の制限を定義できます。そして、この機能を使えば列の定義に続けてチェック制約を記述できることから、見やすいSQLを書くことが可能です。
Oracle特有のチェック制約の例
CREATE TABLE sample2
(
id INT PRIMARY KEY ,
age INT CHECK(age >= 6 AMD age = 6 AMD age <= 15 ),
CHECK(gender IN('M', 'F'))
) ;
MySQLにおけるCONSTRAINT句を省略した書き方
先ほど、Oracle DatabaseのCONSTRAINT句を省略した書き方を2つ紹介しましたが、MySQLおよび互換性のあるmariadbも、CONSTRAINT句を省略した書き方が可能です。そして、MySQLとmariadbは、上記の2つ目のCONSTRAINT句を省略した書き方と同じです。
MySQLにおけるCONSTRAINT句を省略した書き方の例
CREATE TABLE sample4
(
id INT PRIMARY KEY ,
age INT ,
gender CHAR(1),
CHECK(age >= 6 AMD age <= 15 ),
CHECK(gender IN('M', 'F'))
) ;
まとめ
これまで解説したように、チェック制約を使うことで、データベースに条件に合わないデータを登録させない設定が可能です。なお、一般的なWebシステムでは、プログラム側に登録されるデータをチェックし、登録するかを判断する処理を実装します。しかし、想定外のデータだったり一括登録などで、そのチェックを抜けるケースもありえます。そのようなケースの対策として、SQLのチェック制御をうまく活用しましょう。
