トップページ -> 情報工学,情報スキルの教材 -> リレーショナルデータベース入門(実践で学ぶ) -> テーブル定義と一貫性制約
[サイトマップへ]  

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

URL: http://www.kunihikokaneko.com/free/db/2.html


演習で行うこと


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

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

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

  1. SQLite のデータ型

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

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

  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 を新規作成しておく

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

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

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

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

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

  5. SQLite の終了 (End SQLite)

    .exit」で終了.

Ubuntu の場合

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

  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
    

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

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

  5. SQLite の終了 (End SQLite)

    .exit」で終了.


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

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

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 を準備 する.

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

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

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

    ◆ 「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. カーソルキーの「←」と 「 →」 を使いカーソルを間違いのあった場所に動かす。 Del キーや BackSpace キーも使い、間違いを修正. 修正が終わったら Enter キー.

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

Ubuntu の場合

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

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

  3. 別の端末を開く

  4. データベースファイル mydb ができることを確認する.(At the time, database file is generated)
    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. カーソルキーの「←」と 「 →」 を使いカーソルを間違いのあった場所に動かす。 Del キーや BackSpace キーも使い、間違いを修正. 修正が終わったら Enter キー.

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

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

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


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

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

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

datetime('now') は現在日時の取得.datetime型は、YYYY-MM-DD HH:MM:SS形式.

BEGIN TRANSACTION;
INSERT INTO scores VALUES( 1, 'Database',    'K', 'KK', 85, datetime('now'), NULL );
INSERT INTO scores VALUES( 2, 'Database',    'K', 'AA', 75, datetime('now'), NULL );
INSERT INTO scores VALUES( 3, 'Database',    'K', 'LL', 90, datetime('now'), NULL );
INSERT INTO scores VALUES( 4, 'Programming', 'A', 'KK', 85, datetime('now'), NULL );
INSERT INTO scores VALUES( 5, 'Programming', 'A', 'LL', 75, datetime('now'), NULL );
COMMIT;

操作手順の例

  1. 最初にBEGIN TRANSACTION; を実行 (Issue "BEGIN TRANSACTION" before database update).

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

    BEGIN TRANSACTION;
    

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

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

  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'), NULL );
    INSERT INTO scores VALUES( 2, 'Database',    'K', 'AA', 75, datetime('now'), NULL );
    INSERT INTO scores VALUES( 3, 'Database',    'K', 'LL', 90, datetime('now'), NULL );
    INSERT INTO scores VALUES( 4, 'Programming', 'A', 'KK', 85, datetime('now'), NULL );
    INSERT INTO scores VALUES( 5, 'Programming', 'A', 'LL', 75, datetime('now'), NULL );
    

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

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

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

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

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

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

    COMMIT;
    


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

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

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

SELECT * FROM scores;

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

SELECT * FROM scores WHERE name = 'Database';

SELECT score FROM scores WHERE name = 'Database';


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


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

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

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

datetime('now') は現在日時の取得.datetime型は、YYYY-MM-DD HH:MM:SS形式.

BEGIN TRANSACTION;
INSERT INTO orders VALUES( 1, 2018, 4, 26,  'kaneko',   1, 10, datetime('now') );
INSERT INTO orders VALUES( 2, 2018, 4, 26,  'miyamoto', 2, 2, datetime('now') );
INSERT INTO orders VALUES( 3, 2018, 4, 27,  'kaneko',   3, 8, datetime('now') );
INSERT INTO orders VALUES( 4, 2018, 4, 27,  'kaneko',   3, 8, datetime('now') );
COMMIT;

操作手順の例

  1. 最初にBEGIN TRANSACTION; を実行 (Issue "BEGIN TRANSACTION" before database update).

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

    BEGIN TRANSACTION;
    

  2. 行の挿入を行う
    INSERT INTO orders VALUES( 1, 2018, 4, 26,  'kaneko',   1, 10, datetime('now') );
    INSERT INTO orders VALUES( 2, 2018, 4, 26,  'miyamoto', 2, 2, datetime('now') );
    INSERT INTO orders VALUES( 3, 2018, 4, 27,  'kaneko',   3, 8, datetime('now') );
    INSERT INTO orders VALUES( 4, 2018, 4, 27,  'kaneko',   3, 8, datetime('now') );
    

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

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

    COMMIT;
    

補足説明

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

■ 属性の値の並び方を,属性名を使って明示的に指定する (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') );

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

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

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

SELECT * FROM orders;

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

SELECT * FROM orders WHERE day = 26;

SELECT * FROM orders WHERE customer_name = 'kaneko';

SELECT * FROM orders WHERE qty > 8;


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

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

一意制約 (unique)

BEGIN TRANSACTION;
INSERT INTO scores VALUES( 6, 'Database', 'K', 'KK', 75, datetime('now'), NULL );
ROLLBACK;

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

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

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

◆ SQLite3 では、 BEGIN TRANSACTION; を忘れていた としても、 INSERT INTO ... などのデータベース操作は受付られる。但し、 BEGIN TRANSACTION; でトランザクションを開始していないときは、 COMMIT; や ROLLBACK; は受付られない.

主キー制約 (primary key)

BEGIN TRANSACTION;
INSERT INTO orders VALUES( 3, 2018, 4, 28,  'miyamoto', 4, 1, datetime('now') );
ROLLBACK;

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

非空制約 (not null)

BEGIN TRANSACTION;
INSERT INTO orders VALUES( 5, 2018, 4, 28, NULL, 4, 1, datetime('now') );
ROLLBACK;

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

その他の一貫性制約

一貫性制約に違反する例

BEGIN TRANSACTION;
INSERT INTO orders VALUES( 5, 1014, 10, 28,  'miyamoto', 4, 1, datetime('now') );
ROLLBACK;

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

演習問題と解答例

次の問いに答えよ.その後,下記の解答例を確認せよ. 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 の 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 の 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 );