MySQL 資料行與表格限制簡介

資料行限制

資料行限制是附加到單一資料行的限制。它們用於判斷資料行的建議值是否有效。在針對基本類型需求驗證輸入後（例如確保 int 資料行的值是整數），會評估資料行限制。

資料行限制非常適合表達僅限於單一欄位的需求。它們將限制條件直接附加到相關的資料行。例如，我們可以在 person 表格中，透過在資料行名稱和資料類型之後新增限制來建立 age 限制的模型

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

此程式碼片段定義了一個 person 表格，其中一個資料行是名為 age 的 int。 age 必須大於或等於零。資料行限制很容易理解，因為它們會作為額外需求新增到它們影響的資料行上。

表格限制

另一種限制類型稱為表格限制。表格限制幾乎可以表達資料行限制可以表達的任何限制，但另外還可以表達涉及多個資料行的限制。表格限制不是附加到特定資料行，而是定義為表格的個別組件，並且可以參考表格的任何資料行。

我們稍早看到的資料行限制可以表示為如下的表格限制

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 開頭，然後提供以括號括住的運算式。對於資料行限制，這會放在資料類型宣告之後。對於表格限制，這些可以放在與它們互動的資料行定義之後的任何位置。

例如，我們可以建立 film_nominations 表格，其中包含已獲得提名且符合 2019 年劇情長片獎資格的電影

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 年內。之後，我們有一個表格檢查限制，確保電影已收到足夠的票數以獲得提名，且片長符合「劇情長片」類別的資格。

在評估檢查限制時，可接受的值評估為 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 拒絕提交，因為它未通過最終的表格檢查限制。

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 表格限制的優點之一是，它可讓您對資料行組合執行唯一性檢查。其運作方式是指定兩個或多個 MySQL 應一起評估的資料行。個別資料行中的值可能會重複，但指定的值組合必須是唯一的。

舉例來說，讓我們回顧一下我們之前使用的 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
);

這將確保國家/地區和首都都只在每個表格中出現一次。但是，有些國家/地區有多個首都。這表示我們可能會有多個具有相同 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)

但是，嘗試新增相同組合兩次仍然會受到限制的攔截

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 限制具有特殊用途。它表示資料行可用於唯一識別表格中的記錄。這表示它必須是可靠的唯一值，且每筆記錄都必須在該資料行中具有值。

建議每個表格都使用主鍵，但並非必要，且每個表格只能有一個主鍵。主鍵主要用於識別、擷取、修改或刪除表格中的個別記錄。它們允許使用者和管理員使用 MySQL 保證完全符合一個記錄的識別碼來鎖定操作。

讓我們使用我們之前看到的 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)
);

外鍵限制

外鍵是一個表格中參考另一個表格中資料行值的資料行。在表格包含相關資料的各種情境中，這是理想的且通常是必要的。資料庫輕鬆連線和參考儲存在不同表格中的資料的能力，是關聯式資料庫的主要功能之一。

例如，您可能有 orders 表格來追蹤個別訂單，以及 customers 表格來追蹤聯絡資訊和有關客戶的資訊。將此資訊分開放置是有意義的，因為客戶可能有多個訂單。但是，能夠輕鬆連結這兩個表格中的記錄以允許更複雜的操作也是有意義的。

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