やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

SQLite3構文 列制約(unique)

 一意制約。

成果物

unique制約

 unique制約を与えられた列は、そのテーブルがもつ全レコード内で一意の値を持つことが保証される。もし違反したらエラーになる。

 SQLite3においてinteger primary keyとの違いは、自動インクリメントされないこと。もっとも、integer primary key以外のprimary keyは、インデックスを持ったunique制約と同義である。

使いどころ

 インデックスを持たせず一意にしたいとき。

一覧

unique

テーブル作成

0_0.sql

create table T(A text unique);
Error: near line 3: UNIQUE constraint failed: T.A

違反

0_1.sql

create table T(A text unique);
insert into T values('A');
insert into T values('A');
Error: near line 3: UNIQUE constraint failed: T.A

unique on conflict ...

rollback

1_0.sql

create table T(A text unique on conflict rollback);
insert into T values('A');
begin;
insert into T values('B');
insert into T values('B');
insert into T values('C');
end;
select * from T;
Error: near line 5: UNIQUE constraint failed: T.A
Error: near line 6: cannot commit - no transaction is active
A
C

abort

1_1.sql

create table T(A text unique on conflict abort);
insert into T values('A');
begin;
insert into T values('B');
insert into T values('B');
insert into T values('C');
end;
select * from T;
Error: near line 5: UNIQUE constraint failed: T.A
A
B
C

fail

1_2.sql

create table T(A text unique on conflict fail);
insert into T values('A');
begin;
insert into T values('B');
insert into T values('B');
insert into T values('C');
end;
select * from T;
Error: near line 5: UNIQUE constraint failed: T.A
A
B
C

ignore

1_3.sql

create table T(A text unique on conflict ignore);
insert into T values('A');
begin;
insert into T values('B');
insert into T values('B');
insert into T values('C');
end;
select * from T;
A
B
C

 思うのだが、制約をつけておきながら制約を無視するとか、何の意味があるの? エラーを抑制したいなら最初から制約を付けなければいいと思うのだが。

replace

1_4.sql

create table T(A text unique on conflict replace);
insert into T values('A');
begin;
insert into T values('B');
insert into T values('B');
insert into T values('C');
end;
select * from T;
A
B
C

 置換後に一意でなかったらどうなるか。

1_4_0.sql

create table T(A integer primary key, B text unique on conflict replace);
insert into T(A,B) values(1,'A');
begin;
insert into T(A,B) values(2,'B');
insert into T(A,B) values(3,'A');
insert into T(A,B) values(4,'C');
end;
select * from T;
2|B
3|A
4|C

 なんと、主キー値が置き換えられた! これは危険すぎ……。もはやお前が主キー。

 何が危険か。もしこの主キーが他のテーブルの外部キーの参照先として使われていたら、整合性が保てなくなってしまう。対策としてはトリガーを作ることが考えられる。主キーが変更されたとき、参照する側の値も一緒に変更するように。

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで