[DB] CREATE TABLEにおける制約まとめ [SQL]

どうも、tatsuです!

今回はCREATE TABLEにおける制約についてまとめてみました!

CREATE TABLE

まずCREATE TABLEとはデータベースに新たなテーブルを作成するときに使用するSQLになります。
構文は以下の通りです。制約とテーブル制約は無くても構いません。

構文
CREATE TABLE テーブル名(
    列名 データ型 制約,
    ・・・,
    ・・・,
    テーブル制約
)

各種制約

どのデータベースにも主に以下の制約があります。

  • デフォルト値設定(正確には制約ではないが書き方が似ているため合わせて説明)
  • 非NULL制約
  • UNIQUE制約
  • 主キー制約
  • 検査制約
  • 参照制約
また、制約の記述方式には列制約テーブル制約の2種類があります。
両者ともほぼ同じことを実現できますが、以下部分が異なっています。

  • 列制約:列に対する制約なので複数列に一括で制約を設定できない
  • テーブル制約:非NULL制約を設定できない

それでは各制約の定義方法を見ていきましょう。

デフォルト値設定

デフォルト値設定を列に定義しておくと、テーブルにデータ追加時に値を指定しなくてもデフォルト値が追加されます。

構文
CREATE TABLE テーブル名(
    ・・・,
    在庫数 INTEGER(3) DEFAULT 10,
    ・・・
)
これで在庫数列に値を指定しなくてもデフォルトで10が追加されることになります。

非NULL制約

非NULL制約を列に定義しておくと、その列にNULLは入らなくなります。

構文(列制約)
CREATE TABLE テーブル名(
    ・・・,
    在庫数 INTEGER(3) NOT NULL,
    ・・・
)

UNIQUE制約

UNIQUE制約を列に定義しておくと、その列の値は重複値を許さなくなります。
ですがNULLだけは重複してもOKです。

構文(列制約)
CREATE TABLE テーブル名(
    ・・・,
    メールアドレス VARCHAR(100) UNIQUE,
    ・・・
)
構文(テーブル制約)
CREATE TABLE テーブル名(
    ・・・,
    メールアドレス VARCHAR(100),
    ・・・,
    UNIQUE(メールアドレス)
)

主キー制約

主キー制約を列に定義しておくと、その列の値は重複値(NULLを含む)を許さなくなります。
また、1テーブルにつき1回だけ定義可能です。

構文(列制約)
CREATE TABLE テーブル名(
    ・・・,
    管理番号 VARCHAR(8) PRIMARY KEY,
    ・・・
)
構文(テーブル制約)
CREATE TABLE テーブル名(
    ・・・,
    管理番号 VARCHAR(8),
    ・・・,
    PRIMARY KEY(管理番号)
)

検査制約

検査制約を列に定義しておくと、定義した検査条件を満たす値しか列に追加できなくなります。
以下の例では、募金金額には50円以上しか追加できないようにしています。

構文(列制約)
CREATE TABLE テーブル名(
    ・・・,
    募金金額 INTEGER(10) CHECK (募金金額 >= 50),
    ・・・
)
構文(テーブル制約)
CREATE TABLE テーブル名(
    ・・・,
    募金金額 INTEGER(10),
    ・・・,
    CHECK (募金金額 >= 50)
)

参照制約

参照制約は、あるテーブル(参照元テーブル)が他のテーブル(参照先テーブル)の列を参照しているときに有効な制約です。
参照制約を列に定義しておくと、参照先テーブルのデータを削除するときに参照元テーブルでその値を参照していると更新・削除をしないように制約をかけたりすることができます。
 
列制約の場合、定義したい列の後に「REFERENCES 参照するテーブル名(参照する列)」と記述します。

構文(列制約)
CREATE TABLE テーブル名(
    ・・・,
    都道府県コード INTEGER(2) REFERENCES 都道府県テーブル(都道府県コード),
    ・・・,
)
 
テーブル制約の場合、「FOREIGN KEY(列名)」のあとに「REFERENCES 参照するテーブル名(参照する列)」と記述します。
構文(テーブル制約)
CREATE TABLE テーブル名(
    ・・・,
    都道府県コード INTEGER(2),
    ・・・,
    FOREIGN KEY(都道府県コード) REFERENCES 都道府県テーブル(都道府県コード)
)
また、参照制約にはオプションがいくつかあり、それを後ろに記述することで制約をかけることができます。
※上記の例ではオプションを使用していません。その場合、後述の「NO ACTION」が指定されます。
オプション一覧
参照元テーブルに参照先テーブルのデータが存在する場合、以下のような動作をします。

  • ON DELETE NO ACTION ・・・ 参照先データを削除できない
  • ON DELETE CASCADE ・・・ 参照先データの削除に連携して参照元テーブルのデータも削除する
  • ON DELETE SET NULL ・・・ 参照先データの削除に連携して参照元データの列にNULLを設定する
  • ON UPDATE NO ACTION ・・・ 参照先データを更新できない
  • ON UPDATE CASCADE ・・・ 参照先データの更新に連携して参照元テーブルのデータも削除する
  • ON UPDATE SET NULL ・・・ 参照先データの更新に連携して参照元データの列にNULLを設定する
以下の例では都道府県テーブルのデータが削除された場合、元テーブルのデータも削除するような制約をかけています。
構文(列制約+オプション)
CREATE TABLE テーブル名(
    ・・・,
    都道府県コード INTEGER(2) REFERENCES 都道府県テーブル(都道府県コード) ON DELETE CASCADE,
    ・・・,
)

おまけ

各制約には制約名を付与することができます。
制約名を付与することで、後からその制約を削除したい場合に便利になりますのでぜひ覚えておいてください!

制約名をつける

制約名をつけるには、「CONSTRAINT 制約名 制約」と記述します。

構文(列制約)
CREATE TABLE テーブル名(
    ・・・,
    都道府県コード INTEGER(2) CONSTRAINT 制約名 PRIMARY KEY,
    ・・・,
)
構文(テーブル制約)
CREATE TABLE テーブル名(
    ・・・,
    都道府県コード INTEGER(2),
    ・・・,
    CONSTRAINT 制約名 PRIMARY KEY(都道府県コード)
)

制約を削除する

制約を削除する構文は以下になります。

構文
ALTER TABLE テーブル名
    DROP CONSTRAINT 制約名;

まとめ

いかがでしたか?
テーブルに対する制約はデータベーススペシャリスト試験でも頻出の分野なので、この機会にマスターしちゃいましょう!
それでは!

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です