テーブル定義と一貫性制約

【概要】 ドメインは，属性が取りうる値の集合であり，リレーションは，複数のドメインの直積集合の有限部分集合である．これらの概念は，リレーショナルデータベースの基本である．リレーショナルデータベースでは，データベースをテーブルの集まりとして記述する．テーブルの本体にはリレーションを格納できるほか，重複値を持つような多重集合も格納できる．データベースが実世界を正確に反映するためには，一貫性制約が必要である．リレーショナルデータモデルでの一貫性制約には，キー制約，外部キー制約，一意制約，非空制約，ドメイン制約，参照整合性制約などがあり，例えば誕生年に「3009」などの実現不可能な値を禁止することでデータの整合性を確保する．リレーショナルデータベース言語の国際標準がSQLである．SQLは平易な英文として読み下すことができるように設計され，データ操作だけでなく，データベーススキーマの定義やトランザクション管理などの機能も提供する．

大学で使用した自作の資料等を，手直しの上公開している． クリエイティブ・コモンズ BY NC SA．

用語

演習で行うこと

演習で必要となるSQLの基礎知識

SQLは，リレーショナルデータベースの操作を行うための標準言語である． 本章では，SQLiteが実装するSQL の機能のうち，本演習の実施に必要となる要素について解説する．

SQLite 3におけるSQL言語の詳細な仕様については，http://www.hwaci.com/sw/sqlite/lang.html (英語)を参照されたい．

  1. SQLite 3のデータ型システム

    データベース管理システムごとに独自のデータ型システムが定義されている． SQLiteシステムでは，NULL, integer, real, char, text, datetime, bool, BLOBなどのデータ型が利用可能である． 詳細な仕様はhttps://www.sqlite.org/datatype3.htmlに記載されている．各データ型の概要は以下の通りである．

    • NULL: 空値を表現するデータ型 (a NULL value)
    • integer: 8バイト符号付き整数型 (signed integer) ※ SQLiteではBIGINTとintegerは同一の8バイト整数型として実装
    • real: 浮動小数点数型 (floating point value)
    • char, text: 文字列型 (text string) ※ char(n)による最大長指定が可能
    • datetime: 日時データ型

      * datetime型について：SQLiteの内部実装ではtextと同様に可変長文字列として扱われるが，日時データの意味論的な区別のため，適切な型指定が推奨される

    • bool: 論理値型
    • BLOB: バイナリデータ型 (Binary Large Object)．入力データが変換されずそのまま格納される
      * SQLiteではLARGEBLOBとBLOBは同一の大容量バイナリ型として実装

    * SQLite 3のデータ型システムは，SQL標準仕様と比較してより広範なデータ表現が可能である．

  2. SQL文字列リテラルの表記法

    SQL標準仕様では，文字列リテラルはシングルクオーテーション「'」による囲みが規定されている．

  3. SQLテーブル定義文 (create-table-statement) の文法

    create table <table-name> (<column-name> <type-name> [<column constraint> ...], ...);

    * 「[<column constraint> ...]」は省略可能な制約指定を表す

  4. SQL列制約 (column-constraint) の体系

    create-table-statement内で指定可能なデータ整合性制約とデフォルト値の定義

    • データ整合性制約
      • primary key: 主キー制約の指定
      • not null: NULL値の禁止
      • unique: 値の一意性保証
      • references <foreign-table> (<column-name>, ...): 外部キー参照の定義
      • check (<expression>): 値の検証式の指定 ※ SQLite固有機能
    • 既定値と自動採番
      • default (<expression>): 既定値の設定
      • autoincrement: 自動採番機能の有効化
  5. SQLテーブル制約 (table-constraint) の体系

    create-table-statement内で指定する複数列にまたがる制約は，table-constraintとして記述する．

    • primary key(<indexed-column>, ...)
    • unique(<indexed-column>, ...)
    • check(<expression>)
  6. SQL挿入文 (insert statement) の文法

    テーブルへのレコード挿入操作

    • insert into <table-name> values (<expression>, ...);
    • insert into <table-name> (<column-name>, ...) values ( <expression>, ...);
  7. トランザクション制御文：begin transaction, commit, ROLLBACK

    データベース更新操作（SQL挿入文等）の実行時には，「begin transaction;」による開始宣言が必要である． 更新操作の完了後，「commit;」または「ROLLBACK;」による終了処理を行う．

    • commit: 「begin transaction;」以降の全更新操作を確定する
    • ROLLBACK: 「begin transaction;」以降の全更新操作を取り消す
  8. SQL問合せ文における基本句：SELECT, FROM, WHERE

    SQL問合せでは，SELECT, FROM, WHERE句が基本的な構成要素となる．

    • SELECT * FROM <table-name>;

      指定されたテーブルの全レコードを取得する

    • SELECT * FROM <table-name> WHERE <expression>;

      指定されたテーブルから条件式を満たすレコードを選択的に抽出する

SQLite バージョン 3 の起動と終了

Windows の場合

Windows 環境での操作手順について説明します．

  1. まず，Windows にて SQLite 3 用のデータベース・ディレクトリ C:\SQLite を作成します．
  2. SQLite 3を起動します．

    * sqlite3.exe が見つからない場合は，SQLite 3 のダウンロードと sqlite3.exe のセットアップを実施してください．

  3. SQLite 3の起動画面が表示されます．
  4. ヘルプを表示します．

    「.help」コマンドでヘルプ画面が表示されます．

  5. SQLite 3を終了します．

    「.exit」コマンドで終了します．

Ubuntu の場合

Ubuntu 環境での操作手順について説明します．

  1. Ubuntu でターミナルを起動します．
  2. SQLite 3 データベース・ディレクトリに移動します．

    * ホームディレクトリを SQLite 3 データベース・ディレクトリとして使用する場合は， 移動の必要はありません．

  3. SQLite 3を起動します．

    データベース名として mydb を指定します．

    * データベース名は任意ですが，アルファベットのみの使用を推奨します． 

    sqlite3 mydb
  4. ヘルプを表示します．

    「.help」コマンドでヘルプ画面が表示されます．

  5. SQLite 3を終了します．

    「.exit」コマンドで終了します．

SQLite 3 データベースの新規作成と SQL によるテーブル定義・制約の設定

前提条件：SQLite 3 のインストール完了

SQL を使用して，scores テーブルの定義と制約の設定を行います．

リレーショナル・スキーマ：scores(id, name, teacher_name, student_name, score, created_at, updated_at) 

create table scores (
    id            integer  primary key autoincrement not null,
    name          text     not null,
    teacher_name  text     not null,
    student_name  text     not null,
    score         integer  not null check ( score >= 0 AND score <=100 ),
    created_at    datetime not null,
    updated_at    datetime,
    unique (name, student_name) );

SQL のキーワード（create，table，text，not，null，integer，datetime など）は大文字・小文字を区別しません．

テーブル名や列名も大文字・小文字を区別しません．

テーブル名や列名には空白文字を使用できないため，単語間の区切りには「_」を使用します．

text型は文字列型であり，数値演算は実行できません．

Windows の場合

Windows 環境での操作手順について説明します．

  1. SQLite 3を起動します．

    * sqlite3.exe が見つからない場合は，SQLite 3 のダウンロードと sqlite3.exe のセットアップを実施してください．

  2. 「.open --new」コマンドで SQLite 3 データベースを新規作成します．
  3. 「create table ...」コマンドでテーブルを定義します．
    • 空白文字は半角スペースを使用します（全角スペースは使用しません）．
    • 可読性のため複数の空白を使用できます（1個以上の任意の数が使用可能です）．
  4. SQLite 3 データベースファイル mydb が作成されたことを確認します．

    ◆ 「create table ...」コマンド入力時のエラー対処方法について説明します．

    1. 例：「text」を誤って「test」と入力した場合．

      この状態ではEnter キーを押しても入力モードが継続します（応答がないため焦る場合があります）．

    2. セミコロン（;）を入力して Enter キーを押すと，「syntax error」が表示され，入力した「create table scores ( name TEST not null,」はキャンセルされます．
    3. 上下カーソルキーで過去のコマンドを呼び出すことができます．

      使用したいコマンドが表示されたら Enter キーを押します．

    4. 上下カーソルキーで誤りのある行（「name TEST not null,」）を表示します．この時点では Enter キーは押しません．
    5. 左右カーソルキーでカーソルを移動し，Delete キーや Backspace キーで修正します． 修正完了後に Enter キーを押します．

◆ テーブル定義を初期化する必要がある場合は，テーブルを削除するために「drop table ＜テーブル名＞;」というコマンドを実行します．

Ubuntu の場合

  1. 端末で，sqlite3 mydbを実行します．「3」や「mydb」は入力ミスが発生しやすいため，特に注意が必要です． 
    sqlite3 mydb
  2. 次に 「create table ...」を入力して，テーブル定義を実施します．
    • スペースは半角の空白文字を使用します　（全角スペースは使用しません）
    • 可読性を向上させるため，空白を複数個入れることができます．個数に制限はありません（1個でも，2個でも，3個でも可能です）
  3. 新しい端末ウィンドウを起動します
  4. データベースファイル mydb が正常に生成されていることを確認します． 
    ls -al

    ◆ 「create table ...」コマンドの入力時にエラーが発生した場合，以下の手順で対処することができます．

    1. 例えば，「text」を誤って「TEST」と入力してしまった場合（入力エラー），Enterキーを複数回押しても入力モードから復帰できず，システムからの応答がない状態となり，混乱しやすい状況となります．
    2. 半角のセミコロン「;」を入力し，続けてEnterキーを押すと，「syntax error」というエラーメッセージが表示されます． 直前に入力した「create table scores ( name TEST not null,」は無効となります．
    3. 正しい内容で再入力します．カーソルキーの「↑」「↓」を使用すると，過去に入力したコマンドを効率的に呼び出すことができます．

      再利用したいコマンドが表示されたらEnterキーを押します

    4. カーソルキーの「↑」「↓」で，エラーのあった行（「name TEST not null,」）を表示します．この時点ではまだEnterキーは押さないでください
    5. カーソルキーの「←」「→」でカーソルをエラー箇所まで移動し， DeleteキーやBackspaceキーを使用して修正します． 修正完了後，Enterキーを押します．

◆ テーブル定義を初期化する必要がある場合は，テーブルを削除するために「drop table ＜テーブル名＞;」というコマンドを実行します．

* SQLiteでは，データベースの初回使用時にデータベースファイルが自動的に生成されます．生成されるファイル名は，指定したデータベース名と同一となります．

* データベースファイルはテーブル定義などのデータベース更新操作を実行した時点で生成されるため，sqlite3の起動直後にファイルが存在しなくても問題ありません．

SQL を用いたテーブルへの行の挿入

以下のような scores テーブルを作成します．

次のSQLコマンドを使用してscoresテーブルにデータを挿入します．

datetime('now', 'localtime')は現在の日時を取得します．datetime型はYYYY-MM-DD HH:MM:SS形式で表現されます． 

begin transaction;
insert into scores values( 1, 'Database',    'K', 'KK', 85, datetime('now', 'localtime'), NULL );
insert into scores values( 2, 'Database',    'K', 'AA', 75, datetime('now', 'localtime'), NULL );
insert into scores values( 3, 'Database',    'K', 'LL', 90, datetime('now', 'localtime'), NULL );
insert into scores values( 4, 'Programming', 'A', 'KK', 85, datetime('now', 'localtime'), NULL );
insert into scores values( 5, 'Programming', 'A', 'LL', 75, datetime('now', 'localtime'), NULL );
commit;

◆ 具体的な操作手順

  1. はじめにbegin transaction;を実行します．

    セミコロン「;」の入力を忘れないようにご注意ください 

    begin transaction;

    * セミコロン「;」を入力し忘れた場合，Enterキーを複数回押しても入力モードから復帰できず，システムからの応答がない状態となり，混乱しやすい状況となります．

    半角のセミコロン「;」を入力し，続けてEnterキーを押すと，「begin transaction」コマンドが処理されます．

  2. データの挿入

    このデータ挿入では，テーブル定義で指定した順序に従って，全ての属性値を記述します． 

    insert into scores values( 1, 'Database',    'K', 'KK', 85, datetime('now', 'localtime'), NULL );
insert into scores values( 2, 'Database',    'K', 'AA', 75, datetime('now', 'localtime'), NULL );
insert into scores values( 3, 'Database',    'K', 'LL', 90, datetime('now', 'localtime'), NULL );
insert into scores values( 4, 'Programming', 'A', 'KK', 85, datetime('now', 'localtime'), NULL );
insert into scores values( 5, 'Programming', 'A', 'LL', 75, datetime('now', 'localtime'), NULL );

    * 類似したコマンドを複数回入力する場合，効率的な方法があります． カーソルキーの「↑」「↓」を使用すると，過去に入力したコマンドを効率的に呼び出すことができます．

    カーソルキーの「←」「→」， DeleteキーやBackspaceキーで必要な修正を行えます． 修正完了後，Enterキーを押します．

* insert intoコマンド実行時に構文エラーが発生してエラーメッセージが表示された場合， 以下の例のような状況となります．

全体をやり直す必要はなく，エラーが発生した行のみを再実行します．

  • 全てのデータ挿入が完了したら，commit;を実行します．

    セミコロン「;」の入力を忘れないようにご注意ください 

    commit;

    SQL問い合わせの実行と結果の検証

    SQL問い合わせの詳細な解説は，別のWebページで行う．ここでは，テーブルの内容を確認することに焦点を当てる．

    テーブルの全行を表示する方法 

    SELECT * FROM scores;

    特定の条件を満たす行のみを表示する方法 

    SELECT * FROM scores WHERE name = 'Database';

    SELECT score FROM scores WHERE name = 'Database';

    SQLによるテーブル定義と制約の設定

    SQLを使用して，ordersテーブルの定義と制約の設定を行う．

    リレーショナル・スキーマ: orders(id, year, month, day, customer_name, product_id, qty, created_at) 

    create table orders (
    id            integer  primary key autoincrement not null,
    year          integer  not null check ( year > 2008 ),
    month         integer  not null check ( month >= 1 AND month <= 12 ),
    day           integer  not null check ( day >= 1 AND day <= 31 ),
    customer_name text  not null,
    product_id    text  not null,
    qty           integer  not null default 1 check ( qty > 0 ),
    created_at    datetime not null );

    SQLを用いたテーブルへのデータ挿入

    以下のようなordersテーブルを作成する．

    次のSQLを使用してordersテーブルへのデータ挿入を実行する (Insert rows into table 'orders' using SQL)

    datetime('now', 'localtime')は現在の日時を取得する関数で，datetime型はYYYY-MM-DD HH:MM:SS形式で表現される． 

    begin transaction;
insert into orders values( 1, 2022, 1, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );
insert into orders values( 2, 2022, 1, 26,  'miyamoto', 2, 2, datetime('now', 'localtime') );
insert into orders values( 3, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
insert into orders values( 4, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
commit;

    ◆ 具体的な操作手順

    1. トランザクションを開始するため，begin transaction;を実行する

      末尾のセミコロン「;」の入力を忘れないように注意する 

      begin transaction;
    2. データの挿入を実行する 
      insert into orders values( 1, 2022, 1, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );
insert into orders values( 2, 2022, 1, 26,  'miyamoto', 2, 2, datetime('now', 'localtime') );
insert into orders values( 3, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
insert into orders values( 4, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
    3. すべての挿入操作が完了したら，commit;を実行してトランザクションを確定する．

      末尾のセミコロン「;」の入力を忘れないように注意する 

      commit;

    補足説明

    insert into文には2つの記述方法がある．(Two styles of "insert into")

    * テーブル定義の順序に従って，すべての属性値を指定する方法 

    insert into orders values( 1, 2014, 10, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );

    * 属性名リストを使用して，属性値の順序を明示的に指定する方法

    この方法では，属性値を省略した場合，テーブル定義時に設定されたデフォルト値が適用される

    • テーブル定義時にdefaultキーワードで指定した値がデフォルト値となる
    • AUTO INCREMENTが指定されている場合は，自動的に連番が割り当てられる
    insert into orders(id, year, month, day, customer_name, product_id, qty, created_at) values( 1, 2014, 10, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );

    SQL問い合わせの実行と結果の検証

    SQL問い合わせの詳細な解説は，別のWebページで行う．ここでは，テーブルの内容を確認することに焦点を当てる．

    テーブルの全行を表示する方法 

    SELECT * FROM orders;

    特定の条件を満たす行のみを表示する方法 

    SELECT * FROM orders WHERE day = 26;

    SELECT * FROM orders WHERE customer_name = 'kaneko';

    SELECT * FROM orders WHERE qty > 8;

    制約に違反するデータベース更新が許可されないことの確認

    本節では，データベースの制約に違反する更新操作を試行し，データベース管理システムによる一貫性維持機能を確認する．

    一意制約の確認 

    begin transaction;
insert into scores values( 6, 'Database', 'K', 'KK', 75, datetime('now', 'localtime'), NULL );
ROLLBACK;

    * データベース内に既存の行「'Database', 80, 'KK'」が存在するため，一意制約「unique(name, student_name)」に抵触する．

    ◆ ROLLBACK;コマンドは，begin transaction以降に実行された全操作を取り消す．

    上記の例では，insert文が拒否されているため，ROLLBACK;またはcommit;のいずれを実行しても結果は同一となる．ただし，以下の点に注意が必要である．

    • begin transaction;の後，データベース操作完了時には，必ずcommit;またはROLLBACK;でトランザクションを終了する

    ◆ SQLite 3 では，begin transaction;が省略された場合でも，insert into文などのデータベース操作は受け付けられる．ただし，begin transaction;によるトランザクション開始が行われていない場合，commit;やROLLBACK;は実行できない．

    主キー制約の確認 

    begin transaction;
insert into orders values( 3, 2022, 1, 28,  'miyamoto', 4, 1, datetime('now', 'localtime') );
ROLLBACK;

    * id属性に値3が既に存在するため，主キー制約「primary key」に違反する．

    非NULL制約の確認 

    begin transaction;
insert into orders values( 5, 2022, 1, 28, NULL, 4, 1, datetime('now', 'localtime') );
ROLLBACK;

    * customer_name属性には非NULL制約「not null」が設定されているため，NULL値を挿入できない．

    その他の整合性制約

    整合性制約違反の具体例 

    begin transaction;
insert into orders values( 5, 1014, 10, 28,  'miyamoto', 4, 1, datetime('now', 'localtime') );
ROLLBACK;

    * 制約「check ( year > 2008 )」に違反する．

    演習問題と解答例

    以下の問題に回答し，その後に示される解答例を確認すること．

    問題

    1. SQLiteを使用して，以下のSQL文を実行せよ 
      create table SS (
    name          text     not null,
    score         integer  not null check ( score >= 0 AND score <=100 ),
    student_name  text     not null,
    unique(name, student_name) );
      続いて，SQLite 3のSQLを使用して，以下のテーブルを作成せよ 
      table-name: SS
name        | score | student_name
------------------------------------------------------
Database    |   80  | KK
Database    |   95  | AA
Database    |   80  | LL
Programming |   85  | KK
Programming |   75  | LL
    2. 以下のSQL文を実行すると，エラーが発生することを確認せよ 
      (1)
    create table AA (
        id  integer primary key,
        amount integer not null check ( amount > 1000 ) );
    begin transaction;
    insert into AA values ( 1, 100 );
    ROLLBACK;
(2)
    create table BB (
        id  integer primary key,
        name text not null,
        course text not null );
    begin transaction;
    insert into BB values ( 1, 'A', NULL );
    ROLLBACK;
    3. SQLite 3のSQLを使用して，以下のリレーションスキーマに基づくテーブルを定義せよ

      (1) employees(id, employeename, street, city)

      (2) companies(id, companyname, city)

      (3) works(employeename, companyname)

    解答例 (Answers)

    1.   
      insert into SS values('Database', 80, 'KK');
insert into SS values('Database', 95, 'AA');
insert into SS values('Database', 80, 'LL');
insert into SS values('Programming', 85, 'KK');
insert into SS values('Programming', 75, 'LL');
    2. 「insert into AA values ( 1, 100 );」の実行時に Error: constraint failedが発生する
    3.  
      1. employees(id, employeename, street, city) 
        create table employees (
  id integer primary key not null,
  employeename text,
  street text,
  city text );
      2. company(id, companyname, city) 
        create table companies (
  id integer primary key not null,
  companyname text,
  city text );
      3. works(employeename, companyname) 
        create table works (
  employeename text,
  companyname text );