2020年1月24日金曜日

SQLite3 外部キー制約のあるテーブルを作成する

概要

外部キー制約のあるテーブルを作成する方法。
SQLite ver. 3.6.19 からテーブルに外部キー制約が設定できるようになっている。
ただし、毎回(接続ごと)外部キー制約の設定を有効にする必要がある。
(オプションを指定してバイナリをコンパイルすれば、デフォルトで外部キー制約を有効にできるらしい)

注意

親キーとなる項目は 主キーかユニークキーを設定し、indexを作成する。
複合キーでもいい。

外部キー制約の有効/無効を切り替える

環境
  • Windows 10
  • SQLite Command-Line Shell ver 3.29.0

sqlite> -- # 1. 
sqlite> PRAGMA foreign_keys;
0
sqlite> -- # 2. 
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1
sqlite> -- # 3.
sqlite> PRAGMA foreign_keys = OFF;
sqlite> PRAGMA foreign_keys;
0
  1. 現在の外部キー制約が有効か無効かを確認する
    デフォルトは無効。
  2. 外部キー制約を有効にする
  3. 外部キー制約を無効にする

サンプルテーブル

以下のようなデータを想定してテーブルを作成する。
「レシート(receipt)」の「product_id」は 「商品(product)」の「product_id」を外部キーとする。

商品 (product)
product_id product_name price
1 tomato 100
2 potato 80
3 pumpkin 120
レシート (receipt)
row_num create_date product_id quantity
101 2019-12-01 1 5
102 2019-12-02 2 5
103 2019-12-03 1 8
104 2019-12-05 2 3
105 2019-12-08 3 2

外部キー制約のあるテーブルを作成する


PRAGMA foreign_keys = ON;
create table if not exists 'product' (
    product_id     integer primary key autoincrement
    , product_name text    not null
    , price        integer default 0
);
create table if not exists 'receipt' (
    rownum         integer primary key autoincrement
    , create_date  text    default current_timestamp
    , product_id   integer not null
    , quantity     integer default 0
    , foreign key  (product_id) references product(product_id)
);

複合キーのサンプルテーブル

複合キーを外部キーとして指定する場合のサンプル。
以下のようなデータを想定してテーブルを作成する。
「レシート(receipt)」の「product_name」と「producer」は 「商品(product)」の「product_id」と「producer」を外部キーとする。

商品 (product)
product_name producer price
tomato farmerA 100
potato farmerA 80
potato farmerB 85
pumpkin farmerC 120
レシート (receipt)
row_num create_date product_name product_farmer quantity
101 2019-12-01 potato farmerB 5
102 2019-12-02 tomato farmerA 5
103 2019-12-03 pumpkin farmerC 8
104 2019-12-05 potato farmerB 3
105 2019-12-08 tomato farmerA 2

複合外部キー制約のあるテーブルを作成する


PRAGMA foreign_keys = ON;
create table if not exists "product" (
    product_name   text    not null
    , producer     text    not null
    , price        integer default 0
    , primary key (product_name, producer)
);
create table if not exists "receipt" (
    rownum         integer primary key autoincrement
    , create_date  text default current_timestamp
    , product_name text not null
    , producer     text not null
    , quantity     integer default 0
    , foreign key (product_name, producer) references product(product_name, producer)
);

参考URL

Posted by at
Labels: , ,