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

【概要】

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

【目次】

【サイト内の関連ページ】

公開資料は クリエイティブコモンズ 表示-非営利-継承 4.0 国際ライセンス（CC BY-NC-SA 4.0） で提供しており，事前の許可なく自由に利用できます．条件は著作者表示（BY），非営利目的のみ（NC），同一ライセンスでの再配布（SA）です．

用語

演習で行うこと

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

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

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

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

    データベース管理システムごとに独自のデータ型システムが定義されている． SQLiteには，NULL, integer, real, text, BLOBの5つのストレージクラスがある．char，datetime，boolなどの型名も宣言でき，型親和性に基づいて格納される． 詳細な仕様はhttps://www.sqlite.org/datatype3.htmlに記載されている．各データ型の概要は以下のとおりである．

    • NULL: 空値を表現するデータ型 (a NULL value)
    • integer: 符号付き整数型 (signed integer) ※ SQLiteでは値の大きさに応じて1〜8バイトの可変長で格納され，BIGINT，INTなど「INT」を含む型名はすべて同じINTEGER型として扱われる
    • real: 浮動小数点数型 (floating point value)
    • char, text: 文字列型 (text string) ※ char(n)のように括弧内に数値を付けて宣言することはできるが，SQLiteはこの数値による長さ制限を強制しない
    • datetime: 日時データ型

      * datetime型について：SQLiteの型システムでは，宣言された型名に基づいて「型親和性（type affinity）」が決定される．datetimeという型名は，INTEGERやTEXTなど特定の型を示す文字列を含まないため，NUMERIC型親和性が割り当てられる．ただし，日付時刻の文字列（例：'2024-01-01 12:00:00'）を挿入した場合，整数や実数として解釈できないため，実際にはTEXTとして格納される．日時データの意味論的な区別のため，適切な型指定が推奨される．

    • bool: 論理値型
    • BLOB: バイナリデータ型 (Binary Large Object)．入力データが変換されずそのまま格納される

    * 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>): 値の検証式の指定
    • 既定値と自動採番
      • 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 を作成する．
    データベース・ディレクトリ C:\SQLite の作成
  2. SQLite 3を起動する．

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

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

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

    .help コマンドによるヘルプ表示
  5. SQLite 3を終了する．

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

    .exit コマンドによる SQLite 3 の終了

Ubuntu の場合

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

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

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

  3. SQLite 3を起動する．

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

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

    sqlite3 mydb
    sqlite3 mydb の実行
  4. ヘルプを表示する．

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

    .help コマンドによるヘルプ表示
  5. SQLite 3を終了する．

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

    .exit コマンドによる SQLite 3 の終了

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 のセットアップを実施する．

    SQLite 3 の起動
  2. 「.open --new」コマンドで SQLite 3 データベースを新規作成する．
    .open --new コマンドによるデータベースの新規作成
  3. 「create table ...」コマンドでテーブルを定義する．
    • 空白文字は半角スペースを使用する（全角スペースは使用しない）．
    • 可読性のため複数の空白を使用できる（1個以上の任意の数が使用可能である）．
    create table 文による scores テーブルの定義
  4. SQLite 3 データベースファイル mydb が作成されたことを確認する．
    データベースファイル mydb の作成の確認
    ◆ 「create table ...」コマンド入力時のエラー対処方法について説明する．
    1. 例：「text」を誤って「TEST」と入力した場合．

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

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

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

      上下カーソルキーによる過去のコマンドの呼び出し
    4. 上下カーソルキーで誤りのある行（「name TEST not null,」）を表示する．この時点では Enter キーは押さない．
      誤りのある行の表示
    5. 左右カーソルキーでカーソルを移動し，Delete キーや Backspace キーで修正する． 修正完了後に Enter キーを押す．
      カーソルキーによるコマンドの修正
◆ テーブル定義を初期化する必要がある場合は，テーブルを削除するために「drop table ＜テーブル名＞;」というコマンドを実行する．
drop table コマンドの実行

Ubuntu の場合

  1. 端末で，sqlite3 mydbを実行する．「3」や「mydb」は入力ミスが発生しやすいため，注意が必要である． 
    sqlite3 mydb
    sqlite3 mydb の実行
  2. 次に 「create table ...」を入力して，テーブル定義を実施する．
    • スペースは半角の空白文字を使用する（全角スペースは使用しない）
    • 可読性を向上させるため，空白を複数個入れることができる．個数に制限はない
    create table 文による scores テーブルの定義
  3. 新しい端末ウィンドウを起動する
  4. データベースファイル mydb が正常に生成されていることを確認する． 
    ls -al
    ls -al によるデータベースファイル mydb の確認
    ◆ 「create table ...」コマンドの入力時にエラーが発生した場合，以下の手順で対処できる．
    1. 例えば，「text」を誤って「TEST」と入力してしまった場合（入力エラー），Enterキーを複数回押しても入力モードから復帰できず，システムからの応答がない状態となり，混乱しやすい状況となる．
      入力途中のため応答がない状態の画面
    2. 半角のセミコロン「;」を入力し，続けてEnterキーを押すと，「syntax error」というエラーメッセージが表示される． 直前に入力した「create table scores ( name TEST not null,」は無効となる．
      セミコロン入力後の syntax error の表示
    3. 正しい内容で再入力する．カーソルキーの「↑」「↓」を使用すると，過去に入力したコマンドを呼び出すことができる．

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

      上下カーソルキーによる過去のコマンドの呼び出し
    4. カーソルキーの「↑」「↓」で，エラーのあった行（「name TEST not null,」）を表示する．この時点ではまだEnterキーは押さない
      誤りのある行の表示
    5. カーソルキーの「←」「→」でカーソルをエラー箇所まで移動し， DeleteキーやBackspaceキーを使用して修正する． 修正完了後，Enterキーを押す．
      カーソルキーによるコマンドの修正
◆ テーブル定義を初期化する必要がある場合は，テーブルを削除するために「drop table ＜テーブル名＞;」というコマンドを実行する．
drop table コマンドの実行

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

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

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

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

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;
    begin transaction の実行
    * セミコロン「;」を入力し忘れた場合，Enterキーを複数回押しても入力モードから復帰できず，システムからの応答がない状態となり，混乱しやすい状況となる．
    セミコロン入力忘れにより応答がない状態の画面

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

    セミコロン入力後に 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 );
    insert into 文による scores テーブルへの行の挿入
    * 類似したコマンドを複数回入力する場合の効率的な方法がある． カーソルキーの「↑」「↓」を使用すると，過去に入力したコマンドを呼び出すことができる．

    上下カーソルキーによる過去のコマンドの呼び出し

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

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

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

    エラーが発生した行のみの再実行
  3. 全てのデータ挿入が完了したら，commit;を実行する．

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

    commit;
    commit の実行

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

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

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

SELECT * FROM scores;
SELECT * FROM scores の実行結果

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

SELECT * FROM scores WHERE name = 'Database';
SELECT * FROM scores WHERE name = 'Database' の実行結果 
SELECT score FROM scores WHERE name = 'Database';
SELECT score FROM scores WHERE name = 'Database' の実行結果

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

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 );
create table 文による orders テーブルの定義

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

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

orders テーブルの内容

次のSQLを使用してordersテーブルへのデータ挿入を実行する．

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;
    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') );
    insert into 文による orders テーブルへの行の挿入
  3. すべての挿入操作が完了したら，commit;を実行してトランザクションを確定する．

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

    commit;
    commit の実行

補足説明

insert into文には2つの記述方法がある．

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

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

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

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

insert into orders(id, year, month, day, customer_name, product_id, qty, created_at)
values( 1, 2022, 1, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );

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

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

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

SELECT * FROM orders;
SELECT * FROM orders の実行結果

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

SELECT * FROM orders WHERE day = 26;
SELECT * FROM orders WHERE day = 26 の実行結果 
SELECT * FROM orders WHERE customer_name = 'kaneko';
SELECT * FROM orders WHERE customer_name = 'kaneko' の実行結果 
SELECT * FROM orders WHERE qty > 8;
SELECT * FROM orders WHERE qty > 8 の実行結果

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

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

一意制約の確認 

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

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

一意制約違反のエラー表示

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

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

◆ 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値を挿入できない．

非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)

解答例

  1.   
    begin transaction;
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');
commit;
  2.  

    (1) 「insert into AA values ( 1, 100 );」の実行時に，チェック制約「check ( amount > 1000 )」への違反により Error: CHECK constraint failedが発生する

    (2) 「insert into BB values ( 1, 'A', NULL );」の実行時に，course属性の非NULL制約「not null」への違反により Error: NOT NULL 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. companies(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 );