CREATE TABLEと制約 ― 非NULL・一意性・参照・検査

CREATE TABLEとは、新しいテーブルを作るSQL文です。列ごとに名前とデータ型を決め、値について守らせたい決まりを制約として付けます。制約を付けておけば、アプリに不具合があっても、氏名の無い行や会員番号が重なる行をDBMSが受け付けません。データベース設計の論理設計で決めた表は、この文でDBMSに作ります。

CREATE TABLEで書く表の定義は、3層スキーマの概念スキーマに当たります。実行すると、表の定義はDBMSの中のデータディクショナリに、列ごとの行として記録されます。作ったテーブルをほかの利用者に使わせるには、GRANT文で権限を与えます。

CREATE TABLEは、テーブルの構造を作るDDL（データ定義言語）に属する文です。DDLとDMLの分け方は、SELECT文の記事の分類表にあります。主キーと外部キーの考え方は、主キーと外部キーの記事で説明しています。

基本の形では、テーブル名の後のかっこの中に、列の定義を並べます。1つの列の定義は、列名・データ型・制約の順に書きます。定義と定義の間は、カンマで区切ります。

CREATE TABLE テーブル名 (
  列名 データ型 制約,
  列名 データ型 制約,
  複数の列にかかる制約
);

例として、文具店の商品を管理する2つのテーブルを作ります。分類テーブルは商品の分類を持ち、商品テーブルは商品1つずつの情報を持ちます。商品テーブルの定義の各行の意味は、コードの後の表にまとめました。

CREATE TABLE 分類 (
  分類ID  CHAR(2)      PRIMARY KEY,
  分類名  VARCHAR(20)  NOT NULL
);

CREATE TABLE 商品 (
  商品ID  CHAR(4)      PRIMARY KEY,
  商品名  VARCHAR(40)  NOT NULL UNIQUE,
  価格    INTEGER      CHECK (価格 >= 0),
  分類ID  CHAR(2)      NOT NULL,
  発売日  DATE,
  FOREIGN KEY (分類ID) REFERENCES 分類 (分類ID)
);

分類テーブルを先に作るのは、商品テーブルが分類テーブルを参照するためです。参照先のテーブルがまだ無いと、外部キーを定義する文はエラーになります。

データ型は、その列に入れる値の種類を決めます。たとえばINTEGERの列に文字列の abc を入れようとすると、エラーになります。よく使う型は、文字型・数値型・日付型の3つに分けられます。

CHAR(n)は、長さを決めた文字列の型です。n文字より短い値は、後ろを空白で埋めて持ちます。VARCHAR(n)は、n文字までの文字列を持つ型で、短い値も空白で埋めません。

DECIMAL(p, s)は、小数を誤差なく持つ数の型です。pは全体の桁数、sは小数点より右の桁数を表します。表に挙げた型の名前は、どれもSQLの規格で定められています。

制約とは、列に入れる値についてDBMSに守らせる決まりです。制約に違反する行を入れようとすると、DBMSはその文をエラーにします。

非NULL制約は、値が無い状態を禁止する

SQLでは、列に値が入っていない状態をNULLで表します。非NULL制約は、このNULLを禁止する制約で、列の定義にNOT NULLと書きます。商品テーブルでは、商品名と分類IDにNOT NULLを付けています。

一意性制約と主キーは、値の重複を禁止する

一意性制約は、同じ値が2つの行に入ることを禁止します。列の定義にUNIQUEを付けると、その列には行ごとに違う値しか入りません。

ただし、UNIQUEの列でも、NULLなら複数の行に入れられます。PostgreSQLの初期設定とMySQLでは、NULLどうしを重複とみなさないためです。PostgreSQLの公式ドキュメントによると、SQLの規格はこの扱いを製品ごとに決めてよいとしています。

PRIMARY KEYを付けた列は、主キーになります。主キーの列には、一意性制約と非NULL制約の両方がかかります。主キーは、1つのテーブルに1つだけ置けます。

参照制約は、外部キーの値を参照先にある値に限る

参照制約は、外部キーの値を、参照先のテーブルにある値に限る制約です。FOREIGN KEYの後に自分の列を書き、REFERENCESの後に参照先のテーブルと列を書きます。PostgreSQLでは、参照先の列は主キーかUNIQUEを付けた列でなければなりません。

外部キーの列がNULLの行は、参照制約では拒否されません。商品テーブルの分類IDにNOT NULLを付けたのは、分類の無い商品を入れさせないためです。

MySQL 8.4は、列の定義の中にREFERENCESだけを書いた形を無視します。そのため外部キーは、FOREIGN KEYから始まる形で書きます。この記事の例も、この形にしています。

検査制約は、条件が偽になる行を拒否する

検査制約は、列の値が条件を満たすかを確かめる制約です。CHECKの後のかっこに条件を書くと、条件が偽になる行を拒否します。商品コードの桁数のように、コード設計で決めた形も、この制約で確かめられます。

条件の判定が不明になる行は、拒否されません。価格がNULLだと、価格 >= 0 は真とも偽とも決まらないためです。NULLも禁止したい列には、NOT NULLを合わせて付けます。逆にWHERE句では、判定が不明の行は結果に残りません。この違いはSQLのNULLの記事で比べています。

8.0.16より前のMySQLは、CHECKを書いても構文を読むだけで無視していました。古いMySQLでは、条件に反する行もそのまま入ります。

次の図で商品テーブルに入れる行を選ぶと、その行が追加されるか拒否されるかが分かります。拒否される行では、どの制約に違反したかと、関係する定義の行も表示します。

この図は、実際のデータベースには接続していません。結果は、同じSQL文をPostgreSQLで実行して確かめたものです。

CREATE TABLE 分類 (
  分類ID  CHAR(2)      PRIMARY KEY,
  分類名  VARCHAR(20)  NOT NULL
);

CREATE TABLE 商品 (
  商品ID  CHAR(4)      PRIMARY KEY,
  商品名  VARCHAR(40)  NOT NULL UNIQUE,
  価格    INTEGER      CHECK (価格 >= 0),
  分類ID  CHAR(2)      NOT NULL,
  発売日  DATE,
  FOREIGN KEY (分類ID) REFERENCES 分類 (分類ID)
);

INSERT INTO 商品 VALUES ('A003', 'ボールペン', 120, 'B1', DATE '2026-05-01');

INSERT INTO 商品 VALUES ('A004', NULL, 120, 'B1', DATE '2026-05-01');

INSERT INTO 商品 VALUES ('A001', '付箋', 200, 'B2', DATE '2026-05-01');

INSERT INTO 商品 VALUES ('A005', 'ノート', 200, 'B2', DATE '2026-05-01');

INSERT INTO 商品 VALUES ('A006', '定規', 100, 'Z9', DATE '2026-05-01');

INSERT INTO 商品 VALUES ('A007', '鉛筆', -50, 'B1', DATE '2026-05-01');

INSERT INTO 商品 VALUES ('A008', 'クリップ', NULL, 'B1', DATE '2026-05-01');

制約の書き方には、列制約と表制約の2つがあります。列制約は、列の定義の中に書く制約です。表制約は、列の定義とは別の1項目として、カンマで区切って書く制約です。

列制約は、それを書いた列だけにかかります。複数の列の組み合わせにかかる決まりは、表制約で書きます。代表的な例が、2つの列を組み合わせた主キーです。

CREATE TABLE 注文明細 (
  注文ID  CHAR(5),
  行番号  INTEGER,
  商品ID  CHAR(4),
  数量    INTEGER  CHECK (数量 > 0),
  PRIMARY KEY (注文ID, 行番号)
);

注文明細では、1つの注文に複数の行があります。注文IDだけでは行が決まらず、注文IDと行番号の組で1行が決まります。2つの列の両方にPRIMARY KEYを書くと、主キーが2つあることになり、PostgreSQLではエラーになります。

1つの列にかかる主キーは、表制約の形でも書けます。商品テーブルの主キーを PRIMARY KEY (商品ID) と別の行に書いても、意味は同じです。一方で、複数の列を組み合わせた主キーは、列制約の形では書けません。主キーにできる列の組の見つけ方は、主キーと外部キーの記事の候補キーの節にあります。

制約に違反する行を入れようとすると、DBMSはそのINSERT文をエラーにします。行は追加されず、テーブルの中身は文を実行する前のままです。1つのINSERT文で複数の行を入れるときも、PostgreSQLでは1行でも違反があれば文全体がエラーになります。このとき、違反していない行も追加されません。

UPDATE文で値を書き換えるときも、DBMSは制約を確かめます。たとえば商品A002の商品IDをA001に変えるUPDATE文は、主キーの重複になるため拒否されます。

参照されている行の削除も、初期設定では拒否されます。分類B1を消すDELETE文は、B1を指す商品があるためエラーになります。外部キーの定義にON DELETE CASCADEを付けると、参照している行も一緒に削除されます。行を追加・書き換え・削除する文の書き方は、INSERT・UPDATE・DELETE文の記事で説明しています。

参照制約で保たれる、表どうしのつながりの正しさを、参照整合性と呼びます。主キーと外部キーの記事で詳しく説明しています。トランザクションのACID特性のうち、一貫性が守るルールにも、こうした制約が含まれます。

• PostgreSQL公式ドキュメント「Constraints」（各制約の働き、検査制約とNULL、一意性制約とNULL、参照先の列の条件、参照されている行の削除、列制約と表制約）
• PostgreSQL公式ドキュメント「Data Types」・「Character Types」・「Numeric Types」（データ型とSQLの規格の関係）
• MySQL 8.4 Reference Manual「CREATE TABLE Statement」（UNIQUEとNULL、列の定義の中のREFERENCESの扱い）
• MySQL 8.0 Reference Manual「CHECK Constraints」（8.0.16より前のCHECKの扱い）
• MySQL 8.4 Reference Manual「FOREIGN KEY Constraints」（参照されている行の削除の初期設定）