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 是否不為空，以及貸款員是否透過檢查 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 開頭，然後提供一個括在括號中的表示式。對於列約束，它放在資料型別宣告之後。對於表約束，它們可以放在與它們互動的列定義之後的任何位置。

例如，我們可以建立一個 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 年內。之後，我們有一個表檢查約束，確保影片獲得足夠的票數才能被提名，並且長度符合“長片”類別。

在評估 check 約束時，可接受的值將評估為 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 約束更加集中。它保證列中的值不為空。雖然這是一個簡單的約束，但它被頻繁使用。

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 表約束的優點之一是它允許您對列組合執行唯一性檢查。這透過指定 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 應該是唯一的。如果我們要將此列用作主鍵（保證唯一性和非空值），我們可以簡單地將 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
);