[SQLearning][SQLite]データ型と制約の指定

SQL ゼロからはじめるデータベース操作を教材にしてSQLiteを学ぶ
SQL学習連載「SQLearning」の第3回です。

前回: [SQLearning][SQLite]テーブルの作成・確認・削除 | DevAchieve
第3回は「データ型と制約の指定」についてです。

データ型の指定

以下の CREATE 文を例にデータ型と制約の指定について解説します。

CREATE TABLE IF NOT EXISTS products (
    id          INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name        TEXT    NOT NULL UNIQUE,
    description TEXT    NULL,
    price       INTEGER NOT NULL CHECK(price > 0),
    tax_free    INTEGER NOT NULL DEFAULT 0 CHECK(tax_free IN (0, 1)),
    reg_date    TEXT    NOT NULL DEFAULT CURRENT_DATE
);

SQLite では動的型付けシステムを使用していて、データ型はカラムではなく値自体に関連付けられます。
ストレージクラスとして定義されていますが、これらはほぼデータ型と同じと考えることができます。

NULL

NULL値

INTEGER

符号付き整数。桁数によって1, 2, 3, 4, 6 バイトで保存される。

REAL

浮動小数点数。8バイトで保存される。

TEXT

文字列。データベースのエンコーディング(UTF-8, UTF-16BE もしくは UTF-16LE)で保存される。

BLOB

バイナリデータ(Binary Large OBject)。入力データがそのまま保存される。

Boolean

真偽値を格納する Boolean 型はありません。かわりに INTEGER 型の 0(false), 1(true) として保存します。

Date 型と Time 型

Date 型, Time 型, DateTime 型はありません。
かわりに組み込みのDate And Time Functionsが INTEGER, TEXT, REAL のいずれかとして保存します。
INTEGER: 1970-01-01 00:00:00 UTC からの秒数である Unix Time
TEXT: ISO8601 表記の文字列("YYYY-MM-DD HH:MM:SS.SSS")
REAL: 先発グレゴリオ暦にしたがった紀元前4714年11月24日、
グリニッジ標準時における正午からのユリウス日数(関連: ユリウス通日)
REAL はちょっと意味がわかりませんね。

また、SQLite では他の RDBMS との互換性のために Type Affinity (型の類似性)という概念が導入されています。

TEXT: ストレージクラスの NULL, BLOB, TEXT のすべてのデータを含みます。
NUMERIC: ストレージクラスの5つすべてを含みます。データは変換される可能性があります。
INTEGER: NUMERIC とほぼ同じ振る舞いをします。違いは CAST 方法のみです。
REAL: NUMERIC とほぼ同じ振る舞いをします。違いは浮動少数表現を強制する点です。
NONE: 他のストレージクラスへの変換をしません。

Datatypes In SQLite Version 3

制約の指定

NULL と NOT NULL

NULL は NULL値を許容し、NOT NULL は NULL値を入れることができなくなります。

DEFAULT

デフォルト値を設定することができます。
INSERT時の日時をデフォルト値にすることのできる以下のキーワードを設定することもできます。

CURRENT_TIME	HH:MM:SS 形式
CURRENT_DATE	YYYY-MM-DD 形式
CURRENT_TIMESTAMP	YYYY-MM-DD HH:MM:SS 形式

PRIMARY KEY

主キーを設定することができます。INTEGER と組み合わせることで AUTOINCREMENT を実現できます。
しかし、その場合はデータ削除に再び同じ値が入ります。

AUTOINCREMENT

明示的に指定するとデータ削除後は削除した値にはならず次に大きい値が入ります。

UNIQUE

指定すると unique(一意な)値のみを受け入れます。
INSERT または UPDATE 時に既存の値と重複した値を指定するとエラーになります。
NULL を許容する場合、それぞれの NULL値は異なる値として扱われます。

CHECK(条件式)

条件式を満たす値のみを許容します。条件式にサブクエリを含めることはできません。
INSERT または UPDATE 時に条件式を満たさない値を指定するとエラーになります。
ver. 3.3.0 からサポートされました。ver. 3.3.0 以前では指定しても無視されます。

SQLite Query Language: CREATE TABLE