トップページ情報工学を学ぶリレーショナルデータベース(全11回)テーブル定義と一貫性制約

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

大学で使用した自作の教材を,手直しの上公開している. クリエイティブ・コモンズ BY NC SA.

リレーショナルデータベースの基礎であるテーブル定義,一貫性制約,SQL,結合と分解,トランザクション,埋め込みSQL,実行計画,二次索引を学ぶ.SQLite 3 を用いて,SQL についての演習も行う.

【サイト内のリレーショナルデータベース関連の資料】

演習で行うこと

演習を行うために必要になる機能や文法

SQL はリレーショナルデータベース言語の標準である. ここでは SQLite が持つ SQL の機能のうち今回の演習に関係する部分を紹介する。

SQLite 3の SQL の説明は http://www.hwaci.com/sw/sqlite/lang.html (English Web Page) にある.

  1. SQLite 3のデータ型

    データ型の種類は,データベース管理システムごとに違う. SQLite では,NULL, integer, real, char, text, datetime, bool, BLOB などのデータ型を扱うことができる。 詳しい説明は https://www.sqlite.org/datatype3.html にある.要点は下の通り.

    SQLite 3のデータ型と,SQL の標準が定めるデータ型の定義は異なる.大胆にまとめると,SQLite 3のデータ型の方がより大きな範囲のデータを扱える.

  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 の中に含める一貫性制約やデフォルト値の指定

  5. SQL テーブル制約 (table-constraint) の例

    create-table-statement の中に含める一貫性制約のうち複数の属性に関わるものは table-constraint の形で記述することになる.

  6. SQL 挿入文 (insert statement) の例

    テーブルへの行の挿入

  7. begin transaction, commit, ROLLBACK

    SQL 挿入文 (insert statement) などでのデータベース更新を行うときは,最初に「begin transaction;」を実行する. データベース更新が終わったら「commit;」または「ROLLBACK;」を実行する.

  8. SQL の SELECT, FROM, WHERE の例

    SQL での問い合わせ には SELECT, FROM, WHERE 句が多用される.

SQLite バージョン 3 の起動と終了 (Start and end SQLite version 3)

Windows の場合

  1. 前もって Windows で,SQLite 3 データベース・ディレクトリ C:\SQLite を新規作成しておく

    [image]
  2. SQLite 3の起動 (Start the SQLite 3).

    ※ 「sqlite3.exe が無いよ!」というときは SQLite 3 をダウンロードし,sqlite3.exe を準備 する.

    [image]
  3. SQLite 3の画面が開くので確認する

    [image]
  4. ヘルプの表示 (display the help)

    .help」で,ヘルプが表示されるので確認する.

    [image]
  5. SQLite 3の終了 (End SQLite)

    .exit」で終了.

    [image]

Ubuntu の場合

  1. 端末を開く (Open a Terminal)

    [image]
  2. SQLite 3 データベース・ディレクトリに移る.(Move to the database directory).

    ※ ホームディレクトリを SQLite 3 データベース・ディレクトリとして使う場合には、 何も操作する必要はない

  3. SQLite 3の起動 (Start the SQLite 3).

    このとき,データベース名として mydb を指定する.(The logical database name is 'mydb').

    ※ データベース名はなんでも良いが、アルファベットのみを使うのが良い.

    sqlite3 mydb
    

    [image]
  4. ヘルプの表示 (display the help)

    .help」で,ヘルプが表示されるので確認する.

    [image]
  5. SQLite 3の終了 (End SQLite)

    .exit」で終了.

    [image]

SQLite 3 データベースの新規作成 (Create a new SQLite database), SQL を用いたテーブル定義と制約の記述 (Table defintion and constrant specification using SQL)

前準備: SQLite 3 のインストールを終えていること

SQL を用いて,scores テーブルを定義し,制約を記述する.(Define 'scores' table and specify constrants of the table using SQL)

リレーショナル・スキーマ (relational schema): 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 の場合

  1. SQLite 3の起動 (Start the SQLite 3).

    ※ 「sqlite3.exe が無いよ!」というときは SQLite 3 をダウンロードし,sqlite3.exe を準備 する.

    [image]
  2. 「.open --new」で,SQLite 3 データベースの新規作成

    [image]
  3. 次に 「create table ...」を打ち込んで,テーブル定義を行う

    [image]
  4. SQLite 3 データベースファイル mydb の確認.(At the time, database file is generated)

    [image]

    ◆ 「create table ...」をうちこむとき、間違ってしまったとする。今回の演習では、次の方法で回避して欲しい.

    1. 「text」を間違って「test」にしてしまった

      Enterキーを何度押しても、入力モードから抜け出すことができない(システ ムからの反応がないので慌てそうになる)

      [image]
    2. 半角の「;」、「Enter キー」の順に操作する.すると「syntax error」のメッセージが表示される. いま打ち込んだ「create table scores ( name TEST not null,」は破棄される

      [image]
    3. やり直す。カーソルキーの「↑」と 「 ↓」 を活用しよう。過去に入力したコマンドを行単位で呼び出せるので便利.

      再度使いたいコマンドが出てきたら「Enter キー」を押す

      [image]
    4. カーソルキーの「↑」と 「 ↓」 を活用して、間違いのあった行を(「name TEST not null,」)を出している。まだ Enter キーは押さない

      [image]
    5. カーソルキーの「←」と 「 →」 を使いカーソルを間違いのあった場所に動かす。 Del キーや BackSpace キーも使い、間違いを修正. 修正が終わったら Enter キー.

      [image]

    ◆ テーブル定義を消してやり直したいときは、テーブルを駆除するために「drop table <テーブル名>;」と操作する

    [image]

Ubuntu の場合

  1. 端末で、sqlite3 mydbを実行。「3」や「mydb」を忘れやすいので注意。
    sqlite3 mydb
    

    [image]
  2. 次に 「create table ...」を打ち込んで,テーブル定義を行う

    [image]
  3. 別の端末を開く
  4. データベースファイル mydb ができることを確認する.(At the time, database file is generated)
    ls -al
    

    [image]

    ◆ 「create table ...」をうちこむとき、間違ってしまったとする。今回の演習では、次の方法で回避して欲しい.

    1. 「text」を間違って「TEST」にしてしまった(間違った).Enterキーを何度押しても、入力モードから抜け出すことができない(システ ムからの反応がないので慌てそうになる)

      [image]
    2. 半角の「;」、「Enter キー」の順に操作する.すると「syntax error」のメッセージが表示される. いま打ち込んだ「create table scores ( name TEST not null,」は破棄される

      [image]
    3. やり直す。カーソルキーの「↑」と 「 ↓」 を活用しよう。過去に入力したコマンドを行単位で呼び出せるので便利.

      再度使いたいコマンドが出てきたら「Enter キー」を押す

      [image]
    4. カーソルキーの「↑」と 「 ↓」 を活用して、間違いのあった行を(「name TEST not null,」)を出している。まだ Enter キーは押さない

      [image]
    5. カーソルキーの「←」と 「 →」 を使いカーソルを間違いのあった場所に動かす。 Del キーや BackSpace キーも使い、間違いを修正. 修正が終わったら Enter キー.

      [image]

    ◆ テーブル定義を消してやり直したいときは、テーブルを駆除するために「drop table <テーブル名>;」と操作する

    [image]

※ SQLite では,データベースが始めて使うときに,自動的にデータベースファイルが生成される.データベースファイル名は,データベース名と同じになる.

※ データベースファイルが生成されるのは,テーブルを定義するなど,データベースの更新を行ったときなので,最初,sqlite3 を起動したとき,データベースファイルが無くてもあわてないこと.

SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

次のような scores テーブルを作る.(Construct table 'scores)

[image]

以下の SQL を用いてscores テーブルへの行の挿入を行う (Insert rows into table 'scores' using SQL)

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; を実行 (Issue "begin transaction" before database update).

    末尾の「;」を忘れないこと

    begin transaction;
    

    [image]

    ※ 末尾の「;」を忘れたとする.このとき、Enterキーを何度押しても、入力モードから抜け出すことができない(システムからの反応がないので慌てそうになる)

    [image]

    半角の「;」、「Enter キー」の順に操作する. これで「begin transaction」のコマンドが受け付けられる.

    [image]
  2. 行の挿入

    ここでの行の挿入は,属性の値を,テーブル定義の順に全て並べる (List all attribute values. The order is the same as its table definition) 

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

    [image]

    ※ 何度も似たようなコマンドを繰り返すので、楽をしたい. カーソルキーの「↑」と 「 ↓」 を活用しよう。過去に入力したコマンドを行単位で呼び出せるので便利.

    [image]

    カーソルキーの「←」と 「 →」 , Del キーや BackSpace キーを使い、簡単に変更できる. 変更が終わったら Enter キー.

    [image]

    ※ insert into ... のとき、構文エラーがあって、エラーメッセージが出たとする. 例えば、下の例では、エラーメッセージが出ている.

    [image]

    最初からやり直す必要はない。エラーメッセージが出た行だけをやり直す.

    [image]
  3. 行の挿入が全て終わったら commit; を実行する.(Issue "commit" after database update).

    末尾の「;」を忘れないこと

    commit;
    

    [image]

SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

SQL 問い合わせの詳細については,別の Web ページで説明する.ここでは,テーブルの中身を確認して欲しい.

テーブルの全ての行の表示 (List all rows of a table)

SELECT * FROM scores;

[image]

条件を満足する行のみの表示 (List the rows which satisfy a given condition)

SELECT * FROM scores WHERE name = 'Database';

[image]

SELECT score FROM scores WHERE name = 'Database';

[image]

SQL を用いたテーブル定義と制約の記述 (Table defintion and constrant specification using SQL)

SQL を用いて,orders テーブルを定義し,制約を記述する.(Define 'orders' table and specify constrants of the table using SQL)

リレーショナル・スキーマ (relational schema): 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 );

[image]

SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

次のような orders テーブルを作る.(Construct table 'orders)

[image]

以下の SQL を用いてorder records テーブルへの行の挿入を行う (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; を実行 (Issue "begin transaction" before database update).

    末尾の「;」を忘れないこと

    begin transaction;
    

    [image]
  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') );
    

    [image]
  3. 行の挿入が全て終わったら commit; を実行する.(Issue "commit" after database update).

    末尾の「;」を忘れないこと

    commit;
    

    [image]

補足説明

insert into には 2つの方法がある.(Two styles of "insert into")

■ 属性の値を,テーブル定義の順に全て並べる (List all attribute values. The order is the same as its table definition)

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

■ 属性の値の並び方を,属性名を使って明示的に指定する (Specify the order of attribute values using attribute name list)

このとき,属性値を省略すると,テーブル定義のときに指定されたデフォルト値が使われる (defaults values are used)

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 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

SQL 問い合わせの詳細については,別の Web ページで説明する.ここでは,テーブルの中身を確認して欲しい.

テーブルの全ての行の表示 (List all rows of a table)

SELECT * FROM orders;

[image]

条件を満足する行のみの表示 (List the rows which satisfy a given condition)

SELECT * FROM orders WHERE day = 26;

[image]

SELECT * FROM orders WHERE customer_name = 'kaneko';

[image]

SELECT * FROM orders WHERE qty > 8;

[image]

制約に違反する更新ができないことの確認 (constraint violation is not permitted when database update)

ここでは,制約に違反するような更新を試みる.データベース管理システムソフトウェアが一貫性を維持するので, 制約に違反するような更新はできない.

一意制約 (unique)

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

※ すでに「'Database', 80, 'KK'」という行がある. 一意制約「unique(name, student_name)」に違反.

[image]

ROLLBACK; は、「begin transaction 以降に行った全ての操作を取り消す」というコマンド.

上の例では、「insert into scores values( 'Database', 90, 'KK', datetime('now', 'localtime'), NULL );」が受付られていないので、 ROLLBACK;と操作しても、 commit;と操作しても結果は同じであるが、次のことに留意しておく

◆ SQLite 3 では、 begin transaction; を忘れていた としても、 insert into ... などのデータベース操作は受付られる。但し、 begin transaction; でトランザクションを開始していないときは、 commit; や ROLLBACK; は受付られない.

主キー制約 (primary key)

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

※ すでに属性 id には 3 という値がある. 主キー制約「primary key」に違反.

[image]

非空制約 (not null)

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

※ 非空制約「not null」. 属性 customer_name には NULL を入れることができない.

[image]

その他の一貫性制約

一貫性制約に違反する例

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

※ 制約「check ( year > 2008 )」に違反

[image]

演習問題と解答例

次の問いに答えよ.その後,下記の解答例を確認せよ. Answer the following questions. Then, inspect answers described below.

問い (Questions)

  1. SQLite を使い,下記の SQL を評価させなさい (Evaluate the following 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 を使い次のテーブルを作りなさい (Create the following table using SQLite)
    table-name: SS
    name        | score | student_name
    ------------------------------------------------------
    Database    |   80  | KK          
    Database    |   95  | AA         
    Database    |   80  | LL          
    Programming |   85  | KK          
    Programming |   75  | LL         
    
  2. 次の SQL を評価させるとエラーが発生する.確認する (Examine runtime errors of the following 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 を使い,下記のリレーションスキーマに合致するテーブルを定義しなさい (Define tables below using 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('Database', 85, 'KK');
    insert into SS values('Database', 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 );