3.1 データ定義

表(テーブル)は、列(カラム)と行(タプル)から構成されています。この節では、表と列の定義に関する事項ついて説明します。

デフォルト値と制約条件

表を定義する際に、格納されるデータに対してデフォルト値や制約条件を設定しておくと、誤ったデータが格納されたり、必要なデータが欠落したりというような問題が発生する可能性を減らすことができます。デフォルト値と制約条件は、以下のいずれかの方法で設定します。

  • 列制約 --- 1つの列に対して設定する
  • 表制約 --- 1つ以上の列に対して設定する

設定できる項目は以下の通りです。列制約でしか設定できないものと、どちらでも設定できるものがあります。ただし、2つ以上の列に関係するものは表制約でしか設定できません。

列制約で設定
項目 指定方法
デフォルト値 DEFAULT デフォルト値
非NULL制約(空値不可) NOT NULL
一意性制約(重複不可) UNIQUE
検査制約 CHECK (論理式)
主キー制約 PRIMARY KEY
外部キー制約 REFERENCES 参照表名(参照列名 [, ...])

 

表制約で設定
項目 指定方法
一意性制約(重複不可) UNIQUE (列名 [, ...])
検査制約 CHECK (論理式)
主キー制約 PRIMARY KEY (列名 [, ...])
外部キー制約 FOREIGN KEY (列名 [, ...]) REFERENCES 参照表名(参照列名 [, ...])

 

  • デフォルト値は、列に格納する値が指定されていない場合に格納する値を指定します。デフォルト値の設定がない列には空値(NULL値)が入ります。
  • 非NULL制約は、列がNULL値を取らないことを保証します。
  • 一意性制約は、列のデータあるいは複数列のデータを組み合わせたものが、表に存在するすべての行で一意であることを保証します。
  • 検査制約は、特定の列の値が論理式を満たすことを保証します。
  • 主キー制約は、列または複数列の組み合わせが、表内の行を一意に識別するものとして利用できることを保証します。これは非NULLかつUNIQUEの制約と等価です。
  • 外部キー制約は、列または複数列の組み合わせが、他の表(参照表)の列または複数列(参照列)の値と一致することを保証します。これによって関連する2つの表間の参照整合性が維持されます。なお、同時に非NULL制約が設定されてなければ、NULL値の格納は可能です。

CREATE TABLE文

CREATE TABLE文は、データベースに新しい表を作成します。形式は以下の通りです。

CREATE TABLE [IF NOT EXISTS] 表名 (
    列名 データ型 [列制約] [...]
    [, ...]
    [, [CONSTRAINT 制約名] 表制約] [...])

列名とデータ型は1組以上、オプションの列制約は列ごとに複数、オプションの表制約は表に複数指定できます。

  • IF NOT EXISTS を指定すると、既存の表が存在してもエラーになりません。
  • 表名は、作成する新しい表の名前です。
  • 列名は、新しい表を構成する列の名前です。
  • データ型は、列のデータ型です。データ型については別の節で説明します。
  • 列制約は、列に格納する値の制約条件です。
  • 表制約は、表に格納する行(実際には列の値)の制約条件です。
  • 制約名はシステムで自動的に付与されますが、ユーザによる指定も可能です。

具体例として、prod表(商品表)を作成するCREATE TABLE文を以下に示します。

CREATE TABLE prod ( --商品表
    prod_id INTEGER NOT NULL, --商品番号
    prod_name TEXT NOT NULL, --商品名
    price INTEGER DEFAULT 0 CHECK(price >= 0), --価格
    PRIMARY KEY (prod_id));

prod表は3つの列で構成され、prod_id列には非NULL制約を含む主キー制約、prod_name列には非NULL制約、price列にはデフォルト値 0 と検査制約(値は負でない)が設定されています。

次の例は、customer表(顧客表)を作成するCREATE TABLE文です。

CREATE TABLE customer ( --顧客表
    customer_id INTEGER PRIMARY KEY, --顧客番号
    customer_name TEXT NOT NULL); --顧客名

customer表は2つの列で構成され、customer_id列には主キー制約、customer_name列には非NULL制約が設定されています。なお、主キー制約は列制約、表制約のどちらで設定しても同じ効果です。

次の例は、orders表(注文表)を作成するCREATE TABLE文です。

CREATE TABLE orders ( --注文表
    order_id INTEGER UNIQUE, --注文番号
    order_date TIMESTAMP, --注文日
    customer_id INTEGER REFERENCES customer(customer_id), --商品番号
    prod_id INTEGER, --顧客番号
    qty INTEGER, --数量
    FOREGIN KEY (prod_id) REFERENCES prod(prod_id));

orders表は5つの列で構成され、order_id列には一意性制約、customer_id列には外部キー制約(列制約)、prod_id列には外部キー制約(表制約)が設定されています。このため、customer_id列に格納できる値はcustomer表に存在する値、prod_id列に格納できる値はprod表に存在する値でなければなりません。なお、外部キー制約も主キー制約と同様に列制約、表制約のどちらで設定しても同じ効果です。

ALTER TABLE文

ALTER TABLE文は、既存の表の定義を変更します。形式は以下の通りです。

ALTER TABLE [IF EXISTS] 表名
    アクション [, ...]

列および表制約の追加と削除を行うアクションは、以下のいずれかを指定します。

    ADD [COLUMN] 列名 データ型 [列制約] [...]
    DROP [COLUMN] 列名
    ADD [CONSTRAINT 制約名] 表制約
    DROP CONSTRAINT 制約名

 

  • IF EXISTS を指定すると、既存の表が存在しなくてもエラーになりません。
  • 表名は、変更する表の名前です。
  • ADD [COLUMN] は新しい列を追加します。
  • DROP [COLUMN] は既存の列を削除します。
  • ADD [CONSTRAINT] は新しい表制約を追加します。
  • DROP CONSTRAINT は既存の表制約を削除します。

具体例として、customer表に新しい列を追加するALTER TABLE文を以下に示します。

ALTER TABLE customer
    ADD COLUMN address TEXT; --住所

追加されたaddress列には、デフォルト値(NULL値)が格納されます。

次の例は、customer表からaddress列を削除するALTER TABLE文です。

ALTER TABLE customer
    DROP COLUMN address;

ALTER TABLE文には上記以外にも多数の構文があります。マニュアルを参照してください。

DROP TABLE文

DROP TABLE文は、データベースから既存の表を削除します。形式は以下の通りです。

DROP TABLE [IF EXISTS] 表名 [, ...]
  • IF EXISTS を指定すると、既存の表が存在しなくてもエラーになりません。
  • 表名は、削除する表の名前です。複数指定できます。

他の表から外部キー制約によって参照されている表は削除できません。また、表を削除するのではなく、表の行を空にするには DELETE文またはTRUNCATE文を使用してください。

具体例として、orders表を削除するDROP TABLE文を以下に示します。

DROP TABLE orders;

TRUNCATE文

TRUNCATE文は、表に格納されているすべての行を高速に削除します。形式は以下の通りです。

TRUNCATE [TABLE] 表名 [, ...]
  • 表名は、すべての行を削除する表の名前です。複数指定できます。

TRUNCATE文は、条件指定のないDELETE文と同じようにすべての行を削除します。ただし、他の表から外部キー制約によって参照されている場合は削除できません。

具体例として、customer表を空にするTRUNCATE文を以下に示します。

TRUNCATE customer;