超電磁砲でデータベース（テーブル作成編１）

ここまでは既存のテーブルに対して問い合わせしか行ってこなかったが、ここでテーブルを作成することを学んでみよう。

はじめに

今回扱う重要な概念の１つに外部キーがある。SQLite の初期状態では無効になっている。有効かどうかは PRAGMA foreign_keys; とすれば分かる。0 が表示されれば無効、1 が表示されれば有効である。とりあえずは次の命令を実行すれば有効になる。

PRAGMA foreign_keys = ON;

実際に有効化する必要があるのは次回である。

テーブル定義

テーブル定義の機能を盛り込みながらこれまで使用した２つのテーブルを作成し直す。TEXT は文字列、INTEGER は整数を表す型である。

テーブル定義には CREATE TABLE を使う。例えば、テーブル S_TEST を定義する場合には概ね次のようになる。

CREATE TABLE S_TEST (
 NAME TEXT, 
 LEVEL INTEGER, 
 SKILL TEXT
)　

この後、括弧内に幾つかの条件を反映するのが目的である。これらのテーブルに以下の条件を付けよう。

上の条件には、主キー(PRIMARY KEY), NOT NULL, DEFAULT 値, 制約(CHECK), 外部キー(FOREIGN KEY) といった技術が含まれている。順に見て行こう。

主キーはテーブルの行を一意に定める属性である。属性の組でも可。設定しなくてもテーブルを定義することは可能かもしれないが、設計上必要なものであり必ず設定すべきである。通し番号をつけて済ませる事例もあるようだが、本来の役割を果たせず、はなはだ疑問である。主キーの設定は、以下のように記述する。

PRIMARY KEY ( 属性名１, 属性名2, \( \cdots \) )

主キーが１属性からなるときは属性の定義の後ろに PRIMARY KEY と追加して済ませることもできる。

NOT NULL は属性値として NULL を取ることを禁じる。DEFAULT は値を指定しない場合に設定する値を定める。いずれも属性の定義の後ろに記述する。DEFAULT を設定しない場合は DEFAULT 値は NULL である。制約条件は CHECK ( 制約条件 ) で与えることができる。

最後に外部キー。これがテーブル間の関係を与える。テーブル間に適切な関係を与えることでデータベースとしての整合性を保てるわけで極めて重要である。設定法は次のようにする。

FOREIGN KEY ( 属性名 ) REFERENCES 参照先テーブル名 ( 参照先属性名 )

解答例

CREATE TABLE S_TEST(
 NAME TEXT PRIMARY KEY,
 LEVEL INTEGER NOT NULL DEFAULT 0,
 SKILL TEXT,
 CHECK( 0 <= LEVEL ),
 CHECK( LEVEL <= 5 ) 
) ;

CREATE TABLE A_TEST(
 NAME TEXT,
 TEAM TEXT,
 PRIMARY KEY ( NAME, TEAM ),
 FOREIGN KEY ( NAME ) REFERENCES S_TEST( NAME )
) ;

データの登録、更新、削除

テーブルを作成したので、データの登録、更新、削除の実習をする。今回は S_TEST のみを利用するので、上の解答例の左側を実行しておく。

登録
INSERT INTO テーブル名( 属性名１, \(\cdots\) ) VALUES ( 属性値１, \(\cdots\) ) ;
全ての属性を登録順に列挙するときは属性名のリストを省略できるが、順序を間違えても型が適合すればエラーにならないので、属性名を書いておいた方が安全ではある。

更新
UPDATE テーブル名 SET 属性名１=属性値１, \(\cdots\) WHERE 条件 ;

削除
DELETE FROM テーブル名 WHERE 条件 ;

実際に課題を解いて、動作を確認しよう。

解答例
(1)
INSERT INTO S_TEST( NAME, LEVEL, SKILL ) VALUES ( ‘上条当麻’, 0, ‘イマジンブレイカー’ ) ;
INSERT INTO S_TEST VALUES ( ‘上条当麻’, 0, ‘イマジンブレイカー’ ) ; でも可。ただし、( ‘イマジンブレイカー’, 0, ‘上条当麻’ ) のように間違えても型が同じなので登録に成功してしまう。間違いを防ぐために属性名を明記することは意味があるだろう。

(2)
INSERT INTO S_TEST( NAME ) VALUES ( ‘上条当麻’ ) ;
NAME は主キーなので重複が許されない。したがって、エラーとなる。

(3)
INSERT INTO S_TEST( NAME ) VALUES ( ‘初春飾利’ ) ;
SELECT * FROM S_TEST ; で確認すると LEVEL = 0, SKILL が NULL になっていることが分かる。LEVEL は DEFAULT値が 0

(4)
UPDATE S_TEST SET LEVEL = 1, SKILL = ‘サーマルハンド’ WHERE NAME = ‘初春飾利’ ;
WHERE 句で注意しないと想定外のデータが変更される危険性がある。主キーで指定できれば一意性が保証されるため、想定外のデータを誤って修正することを防げる。

(5)
UPDATE S_TEST SET LEVEL = 6 WHERE NAME = ‘上条当麻’ ;
CHECK( LEVEL <= 5 ) の制約に反するためエラーとなる。

(6)
UPDATE S_TEST SET LEVEL = NULL WHERE NAME = ‘上条当麻’ ;
NOT NULL 制約に反するためエラーとなる。

(7)
DELETE FROM S_TEST WHERE NAME = ‘上条当麻’ ;