SQL - 外部索引鍵

---

---

SQL 外部索引鍵

在 SQL 中，外部索引鍵是表中的一列，它與另一表中的主鍵匹配，從而允許將這兩個表連線在一起。

外部索引鍵還維護兩個表之間的參照完整性，使得無法刪除包含主鍵的表（保留表之間的連線）。

外部索引鍵可以引用資料庫中任何表的唯一欄位。具有主鍵的表稱為父表，具有外部索引鍵的表稱為子表。

讓我們考慮一個示例場景，假設我們有兩個表，名為 CUSTOMERS (ID, NAME, AGE, ADDRES, SALARY) 和 ORDERS (ID, DATE, CUSTOMER_ID, AMOUNT)。這裡客戶的 ID 是 CUSTOMERS 表中的主鍵 (ID)，也是 ORDERS (CUSTOMER_ID) 表中的外部索引鍵，請觀察下圖：

外部索引鍵的特性

以下是外部索引鍵的特性：

• 外部索引鍵用於減少表中的冗餘（或重複）。

• 它有助於規範化（或組織資料庫中的資料）多個表中的資料。

語法

以下是為 MySQL 資料庫中表的列新增外部索引鍵約束的基本語法：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT fk_name 
	FOREIGN KEY (column_name) 
	REFERENCES referenced_table(referenced_column)
);

示例

讓我們建立兩個名為 CUSTOMERS 和 ORDERS 的表。以下查詢建立一個名為 CUSTOMERS 的表：

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

輸出

以下是上述 SQL 語句的輸出：

Query OK, 0 rows affected (0.02 sec)

現在，讓我們建立 ORDERS 表。在此過程中，我們在 CUSTOMER_ID 列上新增外部索引鍵約束，引用 CUSTOMERS 表的 ID 列，如下面的語句所示：

CREATE TABLE ORDERS (
   ID INT NOT NULL,
   DATE DATETIME, 
   CUSTOMER_ID INT,
   CONSTRAINT FK_CUSTOMER 
   FOREIGN KEY(CUSTOMER_ID) 
   REFERENCES CUSTOMERS(ID),
   AMOUNT DECIMAL,
   PRIMARY KEY (ID)
);

輸出

上述語句產生以下輸出：

Query OK, 0 rows affected (0.04 sec)

驗證

我們在 ORDERS 表的 CUSTOMER_ID 列上建立了一個外部索引鍵約束，該約束引用了 CUSTOMERS 表的 ID 列；因此，在刪除 table2 (ORDERS) 之前，不能刪除 table1 (CUSTOMERS)。

首先，讓我們使用 DROP TABLE 語句刪除 CUSTOMERS 表而不刪除 ORDERS 表：

DROP TABLE CUSTOMERS; 

如果您驗證下面的錯誤訊息，您會發現它指出該表無法刪除，因為它被外部索引鍵約束引用。

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'FK_CUSTOMER' on table 'orders'.

現有列上的外部索引鍵約束

我們還可以為現有表的列建立外部索引鍵約束。當您在建立表時忘記為列新增外部索引鍵約束，或者即使表中存在一個外部索引鍵列，也希望在此列上新增此約束時，這很有用。

語法

使用 ALTER TABLE 語句，我們可以如下所示在 MySQL 資料庫中表的現有列上新增外部索引鍵約束：

ALTER TABLE TABLE2 
ADD CONSTRAINT[symbol] 
FOREIGN KEY(column_name) 
REFERENCES TABLE1(column_name);

這裡，FK_ORDERS 是外部索引鍵約束的名稱。指定約束名稱是可選的，但在刪除約束時非常方便。

示例

假設 CUSTOMERS 和 ORDERS 表已在 SQL 資料庫中建立。現在，我們將為 ORDERS 表的 ID 列新增外部索引鍵約束。

以下是為現有表的列新增外部索引鍵約束的 SQL 查詢：

ALTER TABLE ORDERS 
ADD CONSTRAINT FK_ORDERS 
FOREIGN KEY(ID) 
REFERENCES CUSTOMERS(ID);

輸出

以下是上述程式的輸出：

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

驗證

我們在 ORDERS 表的 CUSTOMER_ID 列上建立了一個外部索引鍵約束，該約束引用了 CUSTOMERS 表的 ID 列。因此，在刪除 table2 (ORDERS) 之前，不能刪除 table1 (CUSTOMERS)。

首先，讓我們透過執行以下語句刪除 CUSTOMERS 表而不刪除 ORDERS 表：

DROP TABLE CUSTOMERS;

這會生成一條錯誤訊息，指出該表無法刪除，因為它被外部索引鍵約束引用。

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'FK_CUSTOMER' on table 'orders'.

刪除外部索引鍵

您可以使用 ALTER TABLE 語句從表中刪除外部索引鍵，而無需刪除整個表。

語法

以下是使用 ALTER TABLE 語句從表的列中刪除外部索引鍵約束的語法：

ALTER TABLE table_name DROP FOREIGN KEY (constraint symbol);

其中，FK_NAME 是您需要刪除的外部索引鍵約束的名稱。

示例

刪除表列外部索引鍵約束的 SQL 查詢如下：

ALTER TABLE ORDERS DROP FOREIGN KEY FK_ORDERS;

輸出

以下是上述 SQL 查詢的輸出：

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

驗證

由於我們已從 ORDERS 表中刪除了外部索引鍵約束，因此您現在可以直接刪除 CUSTOMERS 表而不刪除 ORDERS 表，如下所示：

DROP TABLE CUSTOMERS;

如果您驗證上述 SQL 命令丟擲的以下狀態程式碼，您會發現 CUSTOMERS 表已刪除。

Query OK, 0 rows affected (0.02 sec)

主鍵與外部索引鍵

即使主鍵和外部索引鍵都引用同一列，它們的工作方式也存在許多差異。這些差異列在下面：