SQLite - ROWIDとカラム制約事項
ROWID
特に指定しなくても、レコードをユニークに決定するROWIDというinteger型のカラムが内部的に付加される。
sqlite> create table AAA(ii integer, tt text); sqlite> insert into AAA values(10, 'aaa'),(20, 'bbb'); sqlite> select rowid,* from AAA; rowid ii tt ---------- ---------- ---------- 1 10 aaa 2 20 bbb
PRIMARY KEY
主キーを指定する。
create table テーブル名( カラム1名 カラム1型名 primary key, ……); create table テーブル名( カラム1名 カラム1型名, ……, primary key(カラム名, …… ));
ひとつのカラムを主キーにする場合はどちらの書き方でも良いが、二つ以上のカラムを主キーにする場合は後者の書き方とする。
主キーの値（複数カラムの場合は値の組み合わせ）が同じレコードを追加することはできない。
注意点として、主キーをNULLにしたレコードは複数追加することが可能。ただし後でNULLを変更した場合に、既に存在する主キーを一致する場合はエラーになる。
sqlite> create table AAA(ii integer, tt text, primary key(ii, tt) ); sqlite> insert into AAA values(10, 'aaa'); sqlite> insert into AAA values(null, 'aaa'); sqlite> insert into AAA values(null, 'aaa'); sqlite> insert into AAA values(40, 'bbb'); sqlite> select rowid,* from AAA; rowid ii tt ---------- ---------- ---------- 1 10 aaa 2 aaa 3 aaa 4 40 bbb sqlite> update AAA set ii=10 where rowid=2; Error: UNIQUE constraint failed: AAA.ii, AAA.tt sqlite> insert into AAA values(20, 'aaa'); sqlite> insert into AAA values(10, 'aaa'); Error: UNIQUE constraint failed: AAA.ii, AAA.tt
INTEGER PRIMARY KEY
INTEGER PRIMARY KEYの場合は自動で増加する値が付加される。
注意点として、INTEGERをINTなどとした場合は付加されない。
最大値のレコードを削除した後に、レコードを追加すると最大値が再利用される。
sqlite> create table AAA(ii integer primary key, tt text); sqlite> insert into AAA values(null, 'aaa'); sqlite> insert into AAA values(null, 'bbb'); sqlite> select * from AAA; ii tt ---------- ---------- 1 aaa 2 bbb sqlite> delete from AAA where tt='bbb'; --最大値のレコードを削除 sqlite> select * from AAA; ii tt ---------- ---------- 1 aaa sqlite> insert into AAA values(null, 'ccc'); sqlite> select * from AAA; ii tt ---------- ---------- 1 aaa 2 ccc -- 最大値の数値が再利用される
INTEGER PRIMARY KEY AUTOINCREMENT
最大値の再利用を指せない様にするには、AUTOINCREMENTを付加する。
AUTOINCREMENTを使用した場合は、最大値を管理するsqlite_sequenceというテーブルが作成される。
なお、INTEGER PRIMARY KEY AUTOINCREMENTを付加したカラムはROWIDと同一になる。
sqlite> create table AAA(ii integer primary key autoincrement, tt text); sqlite> insert into AAA values(null, 'aaa'); sqlite> insert into AAA values(null, 'bbb'); sqlite> select * from AAA; ii tt ---------- ---------- 1 aaa 2 bbb sqlite> delete from AAA where tt='bbb'; sqlite> select * from AAA; ii tt ---------- ---------- 1 aaa sqlite> insert into AAA values(null, 'ccc'); sqlite> select * from AAA; ii tt ---------- ---------- 1 aaa 3 ccc sqlite> .schema CREATE TABLE AAA(ii integer primary key autoincrement, tt text); CREATE TABLE sqlite_sequence(name,seq); sqlite> select rowid,* from AAA; ii ii tt ---------- ---------- ---------- 1 1 aaa 3 3 ccc
NOT NULL
NULL（データの無い状態）を許可しない
sqlite> create table AAA(ii integer not null, tt text);
sqlite> insert into AAA values(null, 'aaa');
Error: NOT NULL constraint failed: AAA.ii
sqlite> insert into AAA(tt) values('bbb');
Error: NOT NULL constraint failed: AAA.ii
UNIQUE
重複したデータ値を許可しない。
SQLiteの場合は、PRIMARY KEYと同じ意味か？
create table テーブル名( カラム1名 カラム1型名 unique, ……); create table テーブル名( カラム1名 カラム1型名, ……, unique(カラム名,……));
sqlite> create table AAA(ii integer, tt text, unique(ii, tt)); sqlite> insert into AAA values(1, 'aaa'); sqlite> insert into AAA values(1, 'bbb'); sqlite> insert into AAA values(2, 'aaa'); sqlite> insert into AAA values(1, 'aaa'); Error: UNIQUE constraint failed: AAA.ii, AAA.tt
DEFAULT
データを省略した場合のデフォルト値を設定する。
sqlite> create table AAA(ii integer default 100, tt text);
sqlite> insert into AAA values(1, 'aaa');
sqlite> insert into AAA values(null, 'bbb');
sqlite> insert into AAA(tt) values('ccc');
sqlite> select * from AAA;
ii tt
---------- ----------
1 aaa
bbb
100 ccc
CEHECK
データを格納する前にチェックを行う
create table テーブル名( カラム1名 カラム1型名 check(条件), ……); create table テーブル名( カラム1名 カラム1型名, ……, check(条件));
sqlite> create table AAA(ii integer, tt text, check(ii>=0 and ii<=9) ); sqlite> insert into AAA values(1, 'aaa'); sqlite> insert into AAA values(-1, 'bbb'); Error: CHECK constraint failed: AAA sqlite> insert into AAA values(10, 'ccc'); Error: CHECK constraint failed: AAA
外部参照制約（FOREIGN KEY）
参照先の外部テーブルのカラムに存在する値のみ書き込むことができる。
SQLiteでは、この機能はデフォルトでOFFなので、データベースファイルに接続する毎にONする必要があるので注意のこと。
sqlite> PRAGMA foreign_keys; --状態確認 foreign_keys ------------ 0 sqlite> PRAGMA foreign_keys = ON; --外部参照を有効にする sqlite> PRAGMA foreign_keys; foreign_keys ------------ 1
テーブルBBBのカラムidをテーブルAAAのuser_idを参照するようにする。
sqlite> PRAGMA foreign_keys = ON; --外部参照を有効にする sqlite> create table AAA(user_id integer primary key autoincrement, name text); sqlite> insert into AAA values(null,'aaa'),(null,'bbb'); sqlite> select * from AAA; user_id name ---------- ---------- 1 aaa 2 bbb sqlite> create table BBB(id integer, rr real, foreign key(id) references AAA(user_id)); sqlite> insert into BBB values(1,1.0); sqlite> insert into BBB values(2,2.0); sqlite> insert into BBB values(3,3.0); Error: FOREIGN KEY constraint failed sqlite> PRAGMA foreign_keys = OFF; --外部参照を無効にする sqlite> PRAGMA foreign_keys; foreign_keys ------------ 0 sqlite> insert into BBB values(3,3.0);--エラーにならない
