メモ：SQLiteに関するあれこれ（制約など）

前回は、SQLiteのDatatypesについてメモを残しました。そのメモの中に非常に「SQLite」らしい特徴があります。こちらの「データベースの各column（列）に割当可能な5つのType Affinity」です。

TEXT affinity： NULL、TEXT、BLOBクラスの値を格納可能。数値を格納する場合、文字列に変換して格納。

NUMERIC affinity： 全クラスの値を格納可能。TEXTクラスの値を格納する場合、INTEGERか REALクラスへの変換を試み、変換できれば変換して格納。変換できなければTEXTクラスのまま格納。NULL、BLOBクラスの値は、変換せずに格納。

INTEGER affinity： 基本的にNUMERIC affinityと同じ動作ですが、CAST expressionsの場合のみ違う動作となります。

REAL affinity： 基本的にNUMERIC affinityと同じ動作ですが、整数形式の値の場合、小数点クラスにして格納します。

BLOB（かつての名称はNONE）affinity： どんなタイプの値でも変換せずに格納します。

さらっと流していましたが、NUMERIC、INTEGER、REALは、自分のクラスへの変換を推奨しますが、変換できなくても、そのまんま格納してしまいます。いちおう「数値型で入れてね」とお願いはするのですが、数値に変換できない文字列を放り込めば、そのまま保存してしまいます。

さらにBLOBに至っては、とりあえず何でもそのまま格納しちゃいます。こんな感じなので、SQLiteで実際にDBを作成する場合、何も決めないまま、とりあえず作成できてしまいます。

そんなSQLiteでも、他のRDBMS同様にDB作成時の制約を設けることはできます。今回は、その制約周りについてざっくりとメモ。

一般的な制約

SQLiteのDB作成時に設定できる制約のうち、下記のものは、他のRDBMSで採用されているものと同じです。

UNIQUE制約：指定した列に同じ値は入れてはダメ
NOT NULL制約：指定した列にNULLを入れてはダメ
PRIMARY KEY制約：指定した列に同じ値は入れてはダメ、NULLもダメ（主キー）
CHECK制約：指定した列に指定した条件以外の値を入れてはダメ
FOREIGN KEY制約：参照関係にある列は互いに整合性を保ってないとダメ（外部キー）
DEFAULT制約：データ追加時に値が省略されていたら、あらかじめ設定しておいた値を格納するよ

SQLite独自という訳ではないので、さらっといきますが、FOREIGN KEY制約については、「version 3.6.19からサポートされた」と下記ページで説明されていたので、Androidだといつからサポートされているのか調べたら、Froyo以降はカバーされていたので、今となっては気にする必要ないですね。

SQLite Foreign Key Support

INTEGER PRIMARY KEY

SQLite独特の挙動として「INTEGER推しの列に PRIMARY KEYを指定した場合の挙動」があります。具体的には、データを追加する際に、INTEGER PRIMARY KEYを設定した列に入れる値を省略しておくと、その列に格納されている最大の数値に「＋1」した値が自動的に割り振られて、DBに格納されるというものです。

ただし、ノーマルなINTEGER PRIMARY KEY設定だと、過去に割り当てられていたが現在は削除されている番号など、現時点で列になければ、重複して番号を割り振って格納します。

これを避けるには、INTEGER PRIMARY KEYに「AUTOINCREMENT」オプションを付けます。これだと、いったん使用した番号が削除されていても、その番号をスキップして、次に大きな番号を割り振って格納してくれます。