MySQLのカラムとテーブル制約入門

カラム制約

カラム制約は、単一のカラムに関連付けられた制約です。これらは、カラムに提案された値が有効かどうかを判断するために使用されます。カラム制約は、入力が基本的な型要件（intカラムの数値が整数であることを確認するなど）に対して検証された後に評価されます。

カラム制約は、単一のフィールドに限定された要件を表現するのに最適です。制約条件を関係するカラムに直接アタッチします。たとえば、personテーブルのage制限を、カラム名とデータ型の後に制約を追加することでモデル化できます。

CREATE TABLE person (
    . . .
    age INT CHECK (age >= 0),
    . . .
);

このスニペットは、カラムの1つがageというintであるpersonテーブルを定義します。ageは0以上である必要があります。カラム制約は、影響を与えるカラムに追加の要件として追加されるため、理解しやすいです。

テーブル制約

もう一方のタイプの制約は、テーブル制約と呼ばれます。テーブル制約は、カラム制約ができるほとんどすべての制限を表現できますが、さらに複数のカラムを含む制限を表現できます。テーブル制約は、特定のカラムに関連付けられるのではなく、テーブルの個別のコンポーネントとして定義され、テーブルの任意のカラムを参照できます。

前に見たカラム制約は、次のようにテーブル制約として表現できます。

CREATE TABLE person (
    . . .
    age INT,
    . . .
    CHECK (age >= 0)
);

同じ基本的な構文が使用されますが、制約は別々にリストされます。テーブル制約が複合制限を導入する機能を活用するために、論理AND演算子を使用して、異なるカラムからの複数の条件を結合できます。

たとえば、銀行データベースでは、qualified_borrowersというテーブルで、個人がローンを受ける資格を得るために既存の口座を持っているかどうか、および担保を提供できるかどうかを確認する必要がある場合があります。これらを同じチェックに含めるのが理にかなっている場合があります。

CREATE TABLE qualified_borrowers (
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK (account_number IS NOT NULL AND acceptable_collateral = true)
);

ここでは、CHECK制約を再度使用して、account_numberがnullではなく、融資担当者がクライアントをacceptable_collateralカラムをチェックすることで許容可能な担保を持っているとマークしていることを確認します。複数のカラムがチェックされているため、テーブル制約が必要です。

これらの例では主にCREATE TABLE SQLコマンドを使用して新しいテーブルを作成しますが、ALTER TABLEを使用して既存のテーブルに制約を追加することもできます。 ALTER TABLEを使用すると、新しい制約により、テーブル内の現在の値が新しい制約に対してチェックされます。値が制約に違反している場合、制約を追加できません。

デフォルトの制約名

上記の構文を使用して制約を作成すると、MySQLは妥当ですが曖昧な名前を自動的に選択します。上記のqualified_borrowersテーブルの場合、MySQLは制約にqualified_borrowers_chk_1という名前を付けます。

INSERT INTO qualified_borrowers VALUES (123, false);

ERROR 3819 (HY000): Check constraint 'qualified_borrowers_chk_1' is violated.

この名前は、制約が違反した場合に、テーブルと制約のタイプに関する情報を提供します。ただし、テーブルに複数の制約が存在する場合、トラブルシューティングに役立つように、より説明的な名前が役立ちます。

カスタム制約名

制約定義の前にCONSTRAINTキーワードとそれに続く名前を付けることで、制約の名前をオプションで指定できます。

カスタム名を追加するための基本的な構文は次のとおりです。

CONSTRAINT <constraint_name> <constraint_type_and_details>

たとえば、qualified_borrowersテーブルの制約にloan_worthinessという名前を付けたい場合は、代わりに次のようにテーブルを定義できます。

CREATE TABLE qualified_borrowers (
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CONSTRAINT loan_worthiness CHECK (account_number IS NOT NULL AND acceptable_collateral = true)
);

これで、制約に違反すると、より説明的なラベルが表示されます。

INSERT INTO qualified_borrowers VALUES (123, false);

ERROR 3819 (HY000): Check constraint 'loan_worthiness' is violated.

カラム制約にも同じ方法で名前を付けることができます。

CREATE TABLE teenagers (
    . . .
    age INT CONSTRAINT is_teenager CHECK (age >= 13 AND age <= 19),
    . . .
);

チェック制約

チェック制約は、カラム値またはテーブル値を含む式をブール値として評価することを指定できる汎用制約です。

チェック制約の例はすでにいくつか見てきました。チェック制約は、キーワードCHECKで始まり、括弧で囲まれた式を提供します。カラム制約の場合、これはデータ型宣言の後に配置されます。テーブル制約の場合、これらは対話するカラムが定義された後であればどこにでも配置できます。

たとえば、2019年の長編賞にノミネートされ、資格のある映画を含むfilm_nominationsテーブルを作成できます。

CREATE TABLE film_nominations (
    title VARCHAR(250),
    director VARCHAR(250),
    release_date DATE CHECK ('2019-01-01' <= release_date AND release_date <= '2019-12-31'),
    length INT,
    votes INT,
    CHECK (votes >= 10 AND length >= 40)
);

1つのカラムチェック制約があり、release_dateが2019年内であることをチェックします。その後、テーブルチェック制約があり、映画がノミネートされるのに十分な票を獲得し、長さが「長編」カテゴリの資格があることを確認します。

チェック制約を評価する場合、許容値はtrueとして評価されます。新しいレコードの値がすべての型要件と制約を満たしている場合、レコードはテーブルに追加されます。

INSERT INTO film_nominations VALUES (
    'A great film',
    'Talented director',
    '2019-07-16',
    117,
    45
);

Query OK, 1 row affected (0.01 sec)

falseと評価される値は、制約が満たされていないことを示すエラーを生成します。

INSERT INTO film_nominations VALUES (
    'A poor film',
    'Misguided director',
    '2019-10-24',
    128,
    1
);

ERROR 3819 (HY000): Check constraint 'film_nominations_chk_2' is violated.

この場合、映画は必要な票数を除いて、すべての条件を満たしています。MySQLは、最後のテーブルチェック制約に合格しないため、送信を拒否します。

非NULL制約

NOT NULL制約は、はるかに焦点を絞っています。カラム内の値がNULLでないことを保証します。これは単純な制約ですが、非常に頻繁に使用されます。

CREATE TABLE national_capitals (
    country VARCHAR(250) NOT NULL,
    capital VARCHAR(250) NOT NULL
);

INSERT INTO national_capitals VALUES (
    NULL,
    'London',
);

ERROR 1048 (23000): Column 'country' cannot be null

CREATE TABLE national_capitals (
    country VARCHAR(250),
    capital VARCHAR(250),
    CHECK (country IS NOT NULL AND capital IS NOT NULL)
);

ユニーク制約

UNIQUE制約は、カラム内の各値が繰り返されてはならないことをMySQLに指示します。これは、複数のレコードで同じ値を持つことが不可能である必要がある多くの異なるシナリオで役立ちます。

たとえば、あらゆる種類のIDを扱うカラムは、定義上、一意の値を持つ必要があります。社会保障番号、学生または顧客ID、または製品UPC（バーコード番号）は、特定の人またはアイテムを区別できない場合、役に立たなくなります。

UNIQUE制約は、カラムレベルで指定できます。

CREATE TABLE supplies (
    supply_id INT UNIQUE,
    name VARCHAR(250),
    inventory INT
);

テーブル制約として指定することもできます。

CREATE TABLE supplies (
    supply_id INT,
    name VARCHAR(250),
    inventory INT,
    UNIQUE (supply_id)
);

UNIQUEテーブル制約を使用する利点の1つは、カラムの組み合わせに対して一意性チェックを実行できることです。これは、MySQLが一緒に評価する必要がある2つ以上のカラムを指定することで機能します。個々のカラムの値は繰り返される可能性がありますが、指定された値の組み合わせは一意である必要があります。

例として、前に使用したnational_capitalsテーブルを振り返ってみましょう。

CREATE TABLE national_capitals (
    country VARCHAR(250) NOT NULL,
    capital VARCHAR(250) NOT NULL,
);

同じペアに対して複数のレコードを追加しないようにしたい場合、ここにUNIQUE制約をカラムに追加できます。

CREATE TABLE national_capitals (
    country VARCHAR(250) NOT NULL UNIQUE,
    capital VARCHAR(250) NOT NULL UNIQUE
);

これにより、国と首都の両方が各テーブルに1回だけ存在することが保証されます。ただし、一部の国には複数の首都があります。これは、同じcountry値を持つ複数のエントリが存在する可能性があることを意味します。これらは現在の設計では機能しません。

INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);
INSERT INTO national_capitals VALUES (
    'Bolivia',
    'La Paz'
);

ERROR 1062 (23000): Duplicate entry 'Bolivia' for key 'national_capitals.country'

個々のカラムで繰り返される値を許可しながら、重複エントリが発生しないようにしたい場合は、countryとcapitalの組み合わせに対する一意性チェックで十分です。

CREATE TABLE national_capitals (
    country VARCHAR(250),
    capital VARCHAR(250),
    UNIQUE (country, capital)
);

これで、ボリビアの首都の両方をエラーなしでテーブルに追加できます。

INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);
INSERT INTO national_capitals VALUES (
    'Bolivia',
    'La Paz'
);

Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

ただし、同じ組み合わせを2回追加しようとすると、制約によってキャッチされます。

INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);
INSERT INTO national_capitals VALUES (
    'Bolivia',
    'Sucre'
);

Query OK, 1 row affected (0.00 sec)
ERROR 1062 (23000): Duplicate entry 'Bolivia-Sucre' for key 'national_capitals.country'

主キー制約

PRIMARY KEY制約は、特別な目的を果たします。これは、カラムをテーブル内のレコードを一意に識別するために使用できることを示します。つまり、確実に一意である必要があり、すべてのレコードがそのカラムに値を持っている必要があります。

主キーはすべてのテーブルに推奨されますが、必須ではなく、すべてのテーブルは1つの主キーしか持つことができません。主キーは主に、テーブル内の個々のレコードを識別、取得、変更、または削除するために使用されます。これにより、ユーザーと管理者は、MySQLによって正確に1つのレコードに一致することが保証されている識別子を使用して操作をターゲットにすることができます。

前に見たsuppliesテーブルを例として使用しましょう。

CREATE TABLE supplies (
    supply_id INT UNIQUE,
    name VARCHAR(250),
    inventory INT
);

ここでは、supply_idが一意である必要があることを特定しました。このカラムを主キーとして使用する場合（一意性と非NULL値を保証）、UNIQUE制約をPRIMARY KEYに変更するだけで済みます。

CREATE TABLE supplies (
    supply_id INT PRIMARY KEY,
    name VARCHAR(250),
    inventory INT
);

このようにして、特定のサプライの在庫量を更新する必要がある場合は、主キーを使用してターゲットにすることができます。

INSERT INTO supplies VALUES (
    38,
    'nails',
    5
);
UPDATE supplies set inventory = 10 WHERE supply_id = 38;

Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

多くのテーブルでは単一のカラムを主キーとして使用しますが、テーブル制約としてカラムのセットを使用して主キーを作成することも可能です。

national_capitalsテーブルは、これを実証するのに適した候補です。既存のカラムを使用して主キーを作成したい場合は、UNIQUEテーブル制約をPRIMARY KEYに置き換えることができます。

CREATE TABLE national_capitals (
    country VARCHAR(250),
    capital VARCHAR(250),
    PRIMARY KEY (country, capital)
);

外部キー制約

外部キーは、あるテーブル内の別のテーブル内のカラム値を参照するカラムです。これは、テーブルが関連データを含むさまざまなシナリオで望ましく、多くの場合必要です。データベースが個別のテーブルに格納されたデータを簡単に接続および参照できるこの機能は、リレーショナルデータベースの主要な機能の1つです。

たとえば、個々の注文を追跡するためのordersテーブルと、顧客の連絡先情報と情報を追跡するためのcustomersテーブルがある場合があります。顧客は多くの注文を持つ可能性があるため、この情報を別々に配置するのが理にかなっています。ただし、これらの2つのテーブルのレコードを簡単にリンクして、より複雑な操作を可能にすることも理にかなっています。

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(250),
    last_name VARCHAR(250),
    phone_number BIGINT,
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE,
    customer BIGINT UNSIGNED,
    FOREIGN KEY (customer) REFERENCES customers(customer_id)
);

INSERT INTO orders VALUES (
    100,
    '2019-11-19',
    300
);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`prisma`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer`) REFERENCES `customers` (`customer_id`))

INSERT INTO customers VALUES (
    300,
    'Jill',
    'Smith',
    5551235677
);
INSERT INTO orders VALUES (
    100,
    '2019-11-19',
    300
);

Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

CREATE TABLE example (
    . . .
    FOREIGN KEY (column1, column2) REFERENCES other_table (column1, column2)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE,
    customer INT,
    FOREIGN KEY (customer) REFERENCES customers(customer_id) ON DELETE CASCADE
);