やってみる

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

📌 ？
トップ > SQLite3 > SQLite3構文　alter（add column）制約

SQLite3構文　alter（add column）制約

SQLite3

　列を追加するとき制約があると様々な罠がある。それらをすべて確認した。

成果物

add column制約の罠

　列制約を与えることができるパターンとできないパターンがある。それを検証してみる。

準備

　ターミナルで以下コマンド実行。

sqlite3

　列を追加するためのテーブルを作る。

create table T(A text, B text);

primary key

alter table T add column C integer primary key;

Error: Cannot add a PRIMARY KEY column

　ドキュメント通り不可であることを確認した。

unique

alter table T add column C integer unique;

Error: Cannot add a UNIQUE column

　ドキュメント通り不可であることを確認した。

default

CURRENT_TIME

alter table T add column C text default CURRENT_TIME;

Error: Cannot add a column with non-constant default

　ドキュメント通り不可であることを確認した。

CURRENT_DATE

alter table T add column C text default CURRENT_DATE;

Error: Cannot add a column with non-constant default

　ドキュメント通り不可であることを確認した。

CURRENT_TIMESTAMP,

alter table T add column C text default CURRENT_TIMESTAMP;

Error: Cannot add a column with non-constant default

　ドキュメント通り不可であることを確認した。

式()

　数式。

alter table T add column C int default (10 - 3);

Error: Cannot add a column with non-constant default

　関数。

alter table T add column C text default (datetime('now','localtime'));

Error: Cannot add a column with non-constant default

　副問合せ。

alter table T add column C text default (select 'A');

Error: near "select": syntax error

alter table T add column C text default (values('A'));

Error: near "values": syntax error

　ドキュメント通り不可であることを確認した。

　ただし、()でも式ではなくリテラル値なら成功してしまった。

　数値リテラル。

alter table T add column C0 int default (1);

select sql from sqlite_master;

CREATE TABLE T(A text, B text, C0 int default (1))

　文字列リテラル。

alter table T add column C1 text default ('A');

select sql from sqlite_master;

CREATE TABLE T(A text, B text, C text default ('A'))

　できちゃった。もとに戻す。

drop table T;
create table T(A text, B text);

他

　ドキュメント通り、上記以外なら成功する。

　数値。

alter table T add column C0 int default 1;

alter table T add column C2 real default -0.5;

　文字列

alter table T add column C3 text default AAA;

alter table T add column C4 text default 'AAA';

alter table T add column C5 text default '1行目
2行目

4行目';

　バイナリ。

alter table T add column C6 text default x'00FF';
関数はダメ

　defaultの値はリテラルのみ可。

alter table T add column C7 text default substr('ABCDEFG', 2, 3);

Error: near "(": syntax error

alter table T add column C8 text default last_insert_rowid();

Error: near "(": syntax error

alter table T add column C9 text default readfile('a.txt');

Error: near "(": syntax error

　もとに戻す。

drop table T;
create table T(A text, B text);

not null

default制約なし

alter table T add column C text not null;

Error: Cannot add a NOT NULL column with default value NULL

　ドキュメント通り不可であることを確認した。

　よしなにやってくれてもいい気がするのだが。既存レコードにはNULLを設定しようとしてしまうから？　やはりnullは無駄にややこしくなるクソ値。

default null

alter table T add column C text not null default NULL;

Error: Cannot add a NOT NULL column with default value NULL

　ドキュメント通り不可であることを確認した。

　これは矛盾しているので当然。

default 非null

alter table T add column C text not null default '';

select sql from sqlite_master;

CREATE TABLE T(A text, B text, C text not null default '')

　成功。not null制約を追加したらデフォルト値を明示せねばならない。

　最初からすべてnot null + default ''でもいいんじゃないかと思えてしまう。でも「未記入」なのか「無記入」なのかの区別が付かない。lockフラグを立てるなども考えられるが、nullの使用も候補に挙がってしまう。事前にユースケース、ワークフローを明確にしてテーブル設計せねばならない。

check制約を追加しても既存の行に対してはチェックしない（将来変更される？）

　もとに戻す。

drop table T;
create table T(A text, B text);

insert into T values('AAA','BBB');

alter table T add column C text check(2<length(C));

　特にエラーなし。

　では、既存レコードのC列における値はどうなっているのか？

.headers on
.mode column
select * from T;

A           B           C         
----------  ----------  ----------
AAA         BBB

　nullなのか空文字列なのか区別がつかない。selectで確認してみる。

select * from T where C = '';







select * from T where C is null;







A           B           C         
----------  ----------  ----------
AAA         BBB                   





　というわけで、追加した新しい列にはnullがセットされる。これはnullを撲滅したい人にとっては嫌な仕様。



　そして、nullはcheck(2<len(C))に違反している。でも既存レコードはcheck対象外。これはドキュメント通りである。将来変更するかもと書いてあったが、バージョン3.29.0ではまだ変更がないようだ。



　念の為、新しく挿入するときはcheckされることを確認する。



insert into T values('2','2','C');







Error: CHECK constraint failed: T





　長さが2より大きければ成功する。



insert into T values('2','2','CCC');













select * from T;







A           B           C         
----------  ----------  ----------
AAA         BBB                   
2           2           CCC       








foreign key references制約を追加したらdefaultでnullを設定する必要がある



　もとに戻す。



drop table T;
create table T(A text, B text);





　既存レコードを作っておく。



insert into T values('AAA','BBB');





　依存先テーブルをつくる



create table U(C integer primary key);





　外部キー制約の可否を確認する。offのため使えない。



.dbconfig







       enable_fkey off
       ...





　外部キー制約を有効化する。



pragma foreign_keys = ON;





　外部キー制約の可否を確認する。onになった。



.dbconfig







       enable_fkey on
       ...





　やっと本題。



alter table T add column C integer references U(C);





　追加した外部キー制約の列値がNULLであれば成功する。先述のとおり、追加した列はdefault未設定ならnullがセットされる。よって今回は成功した。



select sql from sqlite_master;







CREATE TABLE T(A text, B text, C integer references U(C))







.headers on
.mode column
select * from T where C is null;







A           B           C         
----------  ----------  ----------
AAA         BBB                   





非nullをdefaultにセット



　もしdefault値にnull以外をセットしたらエラーになるはず。やってみる。



　まず、テーブルをもとに戻す。



drop table T;
create table T(A text, B text);





　外部キー制約ON。



pragma foreign_keys = ON;





　外部キー制約を与えた上で、defaultで非nullな値をセットする。



alter table T add column C integer default 100 references U(C);







Error: Cannot add a REFERENCES column with non-NULL default value





　ドキュメント通り不可であることを確認した。



所感



　こんなことをイチイチ気にしなくちゃいけないの？



　じつは別の方法がある。新テーブルを作ってデータをコピーして旧テーブルを削除する方法。前回はその概要を確認したが、次回から細かく確認していく。



対象環境






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





前回まで