MySQLの列とテーブルの制約入門

列制約

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

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

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

このスニペットは、ageというint型の列を持つpersonテーブルを定義しています。ageは0以上でなければなりません。列制約は、影響を与える列に追加要件として追加されるため、理解しやすいです。

テーブル制約

もう1つの種類の制約はテーブル制約と呼ばれます。テーブル制約は、列制約が表現できるほとんどすべての制限を表現できますが、さらに複数の列を含む制限も表現できます。テーブル制約は特定の列に付加されるのではなく、テーブルの独立したコンポーネントとして定義され、テーブルの任意の列を参照できます。

先に見た列制約は、このようにテーブル制約として表現できます。

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制約

CHECK制約は、列またはテーブルの値を含む式をブール値として評価できる汎用制約です。

先にCHECK制約のいくつかの例を見ました。CHECK制約はキーワード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)
);

まず、release_dateが2019年以内であることをチェックする列チェック制約があります。その後、映画がノミネートに十分な票を獲得しており、長さが「長編」カテゴリの資格を満たしていることを保証するテーブルチェック制約があります。

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

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

Query OK, 1 row affected (0.01 sec)

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

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は最終的なテーブルCHECK制約を通過しないため、提出を拒否します。

NOT 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制約

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制約

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)
);

FOREIGN KEY制約

外部キーは、あるテーブル内の列であり、別のテーブル内の列値を参照します。これは、関連するデータを含むテーブルが存在するさまざまなシナリオで望ましく、しばしば必要とされます。データベースが別々のテーブルに保存されたデータを簡単に接続し参照できるこの能力は、リレーショナルデータベースの主要な機能の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
);