やってみる

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

📌 ？
トップ > SQLite3 > SQLite3構文　列制約（check）

SQLite3構文　列制約（check）

SQLite3

　条件式。

成果物

check制約

  • 指定した式が偽なら制約違反でエラー
  • 参照できる列は自分の列のみ（表制約なら全列参照できる）

check(expr)

0.sql

create table T(A text check(A = 'A'));
insert into T values('a');
insert into T values('A');
select * from T;

Error: near line 2: CHECK constraint failed: T
A

　A列の値がAならば許可する。それ以外はcheck制約違反。

　違反後は強制終了されず、引き続き処理できるらしい。

比較演算子

  • =, ==
  • !=, <>
  • >,>=
  • <,<=
  • is, is not

=, ==

1_0_0.sql

create table T(A integer check(A = 0));
insert into T values(1);
insert into T values(0);
select * from T;

Error: near line 2: CHECK constraint failed: T
0

!=, <>

1_1_0.sql

create table T(A integer check(A != 0));
insert into T values(1);
insert into T values(0);
select * from T;

Error: near line 3: CHECK constraint failed: T
1

1_1_1.sql

create table T(A integer check(A <> 0));
insert into T values(1);
insert into T values(0);
select * from T;

Error: near line 3: CHECK constraint failed: T
1

>,>=

1_2_0.sql

create table T(A integer check(A > 0));
insert into T values(1);
insert into T values(0);
select * from T;

Error: near line 3: CHECK constraint failed: T
1

1_2_1.sql

create table T(A integer check(A >= 0));
insert into T values(-1);
insert into T values(0);
select * from T;

Error: near line 2: CHECK constraint failed: T
0

<,<=

1_3_0.sql

create table T(A integer check(A < 0));
insert into T values(-1);
insert into T values(0);
select * from T;

Error: near line 3: CHECK constraint failed: T
-1

1_3_1.sql

create table T(A integer check(A <= 0));
insert into T values(1);
insert into T values(0);
select * from T;

Error: near line 2: CHECK constraint failed: T
0

is, is not

1_4_0.sql

create table T(A integer check(A is NULL), B text);
insert into T values(-1, 'A');
insert into T values(NULL, 'B');
select * from T;

Error: near line 2: CHECK constraint failed: T
|B

1_4_1.sql

create table T(A integer check(A is not NULL), B text);
insert into T values(-1, 'A');
insert into T values(NULL, 'B');
select * from T;

Error: near line 3: CHECK constraint failed: T
-1|A

論理演算子

  • and
  • or

and

2_0.sql

create table T(A integer check(0 < A and A < 100));
insert into T values(100);
insert into T values(1);
select * from T;

Error: near line 2: CHECK constraint failed: T
1

or

2_1.sql

create table T(A integer check(A = 0 or A = 1));
insert into T values(100);
insert into T values(1);
select * from T;

Error: near line 2: CHECK constraint failed: T
1

like

3_0.sql

create table T(A text check (A not like '%ABC%'));
insert into T values('1ABC2');
insert into T values('1AAA2');
select * from T;

Error: near line 2: CHECK constraint failed: T
1AAA2

glob

3_1.sql

create table T(A text check (A not glob '*ABC*'));
insert into T values('1ABC2');
insert into T values('1AAA2');
select * from T;

Error: near line 2: CHECK constraint failed: T
1AAA2

regexp

3_2.sql

create table T(A text check (A not regexp '\d{4}-\d{2}-\d{2}'));
insert into T values('200-001-001');
insert into T values('2000-01-01');
select * from T;

Error: near line 3: CHECK constraint failed: T
200-001-001

between

3_3.sql

create table T(A integer check(A not between 0 and 3));
insert into T values(4);
insert into T values(3);
select * from T;

Error: near line 3: CHECK constraint failed: T
4

in

3_4.sql

create table T(A integer check(A not in (2, 4, 6)));
insert into T values(2);
insert into T values(3);
select * from T;

Error: near line 2: CHECK constraint failed: T
3

exists

3_5.sql

create table T(A integer check(not exists (select 1 where A = 1)));
insert into T values(2);
insert into T values(1);
select * from T;

Error: near line 1: subqueries prohibited in CHECK constraints

　check制約内でサブクエリは禁止されている。

窓関数

　窓関数を未学習のため未確認。

対象環境

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

前回まで