サイトマップ サイト内検索 まとめ

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

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

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

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

  • リレーショナルデータベースの基本まとめ：別ページ »で説明
  • SQL 入門演習（SQLite 3 を使用）（全３回）：別ページ »にまとめ

    入門者向け．リレーショナルデータベースを知る．

  • リレーショナルデータベースシステム（スライド資料と SQL 演習）（全15回）：別ページ »にまとめ

    入門者向け．設計と利用と運用．MySQL, Access を利用．基本を学ぶ．演習付き．

  • リレーショナルデータベース演習（全１５回）：別ページ »にまとめ

    入門者向け．設計と利用に関する演習．

  • リレーショナルデータベースの基礎（まとめ） [PDF], [ワードファイル]

    基礎を１つにまとめた資料．

演習で行うこと

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

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 にある．要点は下の通り．

    • NULL: 空値 (a NULL value)
    • integer: 符号付きの整数 (signed integer) ※ SQLite では BIGINT と書いても integer と書いても同じ「８バイトの整数」という意味
    • real: 浮動小数点値 (floating point value)
    • char, STRONG>text: 文字列 (text string) ※ char(n) のように，最大の長さを指定することもある．
    • datetime: 日付や時刻など

      * 日時を示す datetimeについて．SQLite では datetime と書いても text と書いても同じ「可変長文字列」の意味．だけど使い分けるべき（その方が分かりやすい）

    • <bool: ブール値
    • BLOB: バイナリ・ラージ・オブジェクト (Binary Large Object). 入力がそのままの形で格納される (stored exactly as it was inpu * SQLite では LARGEBLOB と書いても BLOB と書いても同じ「長大なバイナリ・ラージ・オブジェクト」という意味

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

    • 一貫性制約
      • primary key ・・・ 主キー
      • not null ・・・ 非空
      • unique ・・・ 一意
      • references <foreign-table> (<column-name>, ...) ・・・ 参照整合性制約
      • check (<expression>) ・・・ 更新時にチェックされる式 ( expression) ※ SQLite 固有の機能
    • デフォルト値，自動インクリメント
      • default (<expression>) ・・・ デフォルト値 (default value) の指定
      • autoincrement ・・・ 自動インクリメント (auto increment)
  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 挿入文 (insert statement) などでのデータベース更新を行うときは，最初に「begin transaction;」を実行する． データベース更新が終わったら「commit;」または「ROLLBACK;」を実行する．

    • commit: ・・・ 「begin transaction;」以降の全てのデータベース更新操作を確定したいとき
    • ROLLBACK: ・・・ 「begin transaction;」以降の全てのデータベース更新操作を破棄したいとき
  8. SQL の SELECT, FROM, WHERE の例

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

    • SELECT * FROM <table-name>;

      table-name で指定したテーブルの全ての行を表示

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

      table-name で指定したテーブルのうち expression で指定した条件を満足する行だけを抽出して表示

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

hWindows の場合

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 3の終了 (End SQLite)

    「.exit」で終了．

Ubuntu の場合

Ubuntu を使用する場合は，次のように操作する．

  1. Ubuntu で端末を開く (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 3の終了 (End SQLite)

    「.exit」で終了．

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 の場合

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

    * 末尾の「;」を忘れたとする．このとき、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', '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 );

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

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

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

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

  • 行の挿入が全て終わったら 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', '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;
    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; を実行する．(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', 'localtime') );

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

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

    • テーブル定義のときに 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 問い合わせの発行と評価結果の確認 (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', 'localtime'), NULL );
ROLLBACK;

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

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

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

    • begin transaction; のあと、データベース操作が終わったら、 commit;または ROLLBACK;で、トランザクションを終了させることを忘れないこと

    ◆ 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」に違反．

    非空制約 (not null) 

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

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

    その他の一貫性制約

    一貫性制約に違反する例 

    begin transaction;
insert into orders values( 5, 1014, 10, 28,  'miyamoto', 4, 1, datetime('now', 'localtime') );
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 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 );