MySQL - 外部索引鍵

Table of content
上一頁
下一頁

在 MySQL 中，**外部索引鍵**是表中的一列（或多列組合），其值與另一表的主鍵列的值匹配。因此，使用外部索引鍵，我們可以將兩個錶鏈接在一起。

外部索引鍵也稱為表的引用鍵，因為它可以引用任何定義為唯一的欄位。

具有主鍵的表稱為父表，具有外部索引鍵的表稱為子表。

除了連結到表之外，外部索引鍵約束還透過防止對主鍵表中的資料進行更改而使主鍵表與外部索引鍵表之間的連結失效來確保引用完整性。即，外部索引鍵阻止諸如“刪除表”之類的操作，這些操作將消除兩個表之間的連線。

建立 MySQL 外部索引鍵

我們可以使用 CREATE TABLE 語句中的 CONSTRAINT... FOREIGN KEY... REFERENCES 關鍵字在 MySQL 表上建立外部索引鍵。

語法

以下是新增表列上的外部索引鍵約束的語法：

CREATE TABLE table2(
   column1 datatype,
   column2 datatype,
   ...
   CONSTRAINT constraint_name 
   FOREIGN KEY (column2) 
   REFERENCES table1(column1)
);

示例

讓我們使用 CREATE TABLE 語句建立一個名為 CUSTOMERS 的表：

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

為了演示外部索引鍵，我們需要兩個表，所以讓我們再建立一個表：

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
   CONSTRAINT fk_customers FOREIGN KEY (CUSTOMER_ID)
   REFERENCES CUSTOMERS(ID)
);

驗證

要驗證是否建立了外部索引鍵，讓我們使用以下語句刪除 CUSTOMERS 表，而不刪除 ORDERS 表：

DROP TABLE CUSTOMERS;

顯示錯誤如下：

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

在現有列上建立外部索引鍵

我們還可以使用 ALTER TABLE... ADD CONSTRAINT 語句在現有表的列上建立外部索引鍵約束。

語法

以下是新增現有表上的外部索引鍵約束的語法：

ALTER TABLE table_name2 
ADD CONSTRAINT constraint_name 
FOREIGN KEY(column_name2) 
REFERENCES table_name1(column_name1);

示例

以下是 MySQL 查詢，用於在現有表 ORDERS 的現有列上新增外部索引鍵約束 FK_CUSTOMERS，該約束引用 CUSTOMERS 表的主鍵：

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

輸出

顯示的表結構將在 CUSTOMER_ID 列上包含一個 FOREIGN KEY 約束，如下所示：

欄位 型別 空 鍵 預設值 額外
OID int NO NULL
日期 varchar(20) NO NULL
CUSTOMER_ID int NO MUL NULL
金額 decimal(18,2) YES NULL

驗證

要驗證我們在 ORDERS 上建立的外部索引鍵是否引用了 CUSTOMERS 表，讓我們使用以下語句刪除 CUSTOMERS 表，而不刪除 ORDERS 表：

DROP TABLE CUSTOMERS;

顯示錯誤如下：

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

刪除 MySQL 外部索引鍵

我們還可以刪除在 MySQL 表上建立的外部索引鍵，無論何時在該表中不再需要它。我們可以在 MySQL 中使用 ALTER TABLE... DROP CONSTRAINT 語句來做到這一點。

語法

以下是刪除表中外部索引鍵的語法：

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

示例

使用以下 MySQL 查詢，我們正在從表中刪除外部索引鍵約束：

ALTER TABLE CUSTOMERS DROP CONSTRAINT fk_customers;

驗證

讓我們使用以下查詢驗證外部索引鍵是否已刪除：

DROP TABLE CUSTOMERS;

主鍵與外部索引鍵

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

主鍵 外部索引鍵
主鍵始終是唯一的。 外部索引鍵可以重複。
主鍵不能為 NULL。 外部索引鍵可以為 NULL。
一個表只能包含一個主鍵。 每個表可以有多個外部索引鍵。

使用客戶端程式建立外部索引鍵

我們還可以使用客戶端程式在表字段上應用外部索引鍵約束。

語法

PHP NodeJS Java Python

要在 PHP 程式中應用欄位上的外部索引鍵，我們需要使用 **mysqli** 函式 **query()** 在 CREATE 語句中執行 FOREIGN KEY 關鍵字，如下所示：

$sql = 'CREATE TABLE customers(Customer_Id INT, Customer_Name VARCHAR(30), 
CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID))';
$mysqli->query($sql);

要在 JavaScript 程式中應用欄位上的外部索引鍵，我們需要使用 **mysql2** 庫的 **query()** 函式在 CREATE 語句中執行 FOREIGN KEY 關鍵字，如下所示：

sql = `CREATE TABLE customerss(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID) )`;
con.query(sql);

要在 Java 程式中應用欄位上的外部索引鍵，我們需要使用 **JDBC** 函式 **execute()** 在 CREATE 語句中執行 FOREIGN KEY 關鍵字，如下所示：

String sql = "CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))";
statement.execute(sql);

要在 Python 程式中應用欄位上的外部索引鍵，我們需要使用 **MySQL Connector/Python** 的 **execute()** 函式在 CREATE 語句中執行 FOREIGN KEY 關鍵字，如下所示：

foreign_key_query = 'CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))'
cursorObj.execute(foreign_key_query)

示例

以下是程式：

PHP NodeJS Java Python
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');

$sql = 'CREATE TABLE customerss(Customer_Id INT, Customer_Name VARCHAR(30), 
CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID))';
if ($mysqli->query($sql)) {
    echo "foreign key column created successfully in customers table \n";
}
if ($mysqli->errno) {
    printf("Table could not be created!.
", $mysqli->error);
}
$mysqli->close();

輸出

獲得的輸出如下：

foreign key column created successfully in customers table

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //creating a column that is foreign key!
  sql = `CREATE TABLE customerss(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID) )`;
  con.query(sql);

  //describe table details
  sql = "DESCRIBE TABLE customers";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

輸出

產生的輸出如下所示：

[
    {
      id: 1,
      select_type: 'SIMPLE',
      table: 'customers',
      partitions: null,
      type: 'ALL',
      possible_keys: null,
      key: null,
      key_len: null,
      ref: null,
      rows: 1,
      filtered: 100,
      Extra: null
    }
]  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ForeignKey {
   public static void main(String[] args) {
      String url = "jdbc:mysql://:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Create a foreign key in the customer table...!;
         String sql = "CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))";
         statement.execute(sql);
         System.out.println("Foreign key created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE customer");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+" "
                    +resultSet.getString(3)+ " "+ resultSet.getString(4));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

輸出

獲得的輸出如下所示：

Connected successfully...!
Foreign key created successfully...!
Customer_Id int YES MUL
Customer_Name varchar(30) YES

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
# Create table 
foreign_key_query = '''CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))'''
cursorObj.execute(foreign_key_query)
connection.commit()
print("Foreign key column is created successfully!")
cursorObj.close()
connection.close()

輸出

以下是上述程式碼的輸出：

Foreign key column is created successfully!
列印頁面

上一篇
下一篇
廣告