MySQL - 外键

MySQL 外键约束

在像 MySQL 这样的关系型数据库中，外键（Foreign Key）是一个表中的字段（或一组字段），它唯一地标识了另一个表中的行。简而言之，‘子’表中的外键指向’父’表的主键（Primary Key），从而在它们之间建立了链接。

这个链接不仅仅是为了组织目的；它还强制执行了引用完整性（Referential Integrity）。这个关键概念确保了表之间的关系保持一致性。例如，外键可以阻止您为不存在的客户创建订单，或者删除仍然有活跃订单的客户。

包含主键的表被称为父表（Parent Table）（或被引用表），而包含外键的表被称为子表（Child Table）（或引用表）。

在表创建期间创建外键

定义外键最常见的方式是在 CREATE TABLE 语句中使用 FOREIGN KEY 约束。

语法

CREATE TABLE child_table (
    column1 datatype,
    ...,
    child_fk_column datatype,
    CONSTRAINT fk_constraint_name
        FOREIGN KEY (child_fk_column)
        REFERENCES parent_table(parent_pk_column)
);

示例

让我们模拟一个简单的电子商务场景。首先，我们创建一个 customers 表（父表）。

-- 父表: customers
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    registration_date DATE NOT NULL
) ENGINE=InnoDB;

现在，我们创建一个 orders 表（子表），其中包含一个外键 customer_id，它引用 customers 表中的 id 列。

-- 子表: orders
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
) ENGINE=InnoDB;

注意：两个表都应该使用支持外键的存储引擎，例如 InnoDB（现代 MySQL 中的默认引擎）。

验证

引用完整性现在已激活。如果您尝试删除 customers 表，MySQL 将会阻止它，因为 orders 表依赖于它。

DROP TABLE customers;

此命令将失败并报错，从而保护您数据的完整性：

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

向现有表添加外键

您还可以使用 ALTER TABLE 语句向已创建的表添加外键约束。

语法

ALTER TABLE child_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (child_fk_column)
REFERENCES parent_table(parent_pk_column);

重要提示：在添加约束之前，请确保 child_fk_column 中的所有现有值都在 parent_pk_column 中有对应的匹配项。

配置 ON UPDATE 和 ON DELETE 操作

可以配置外键，使其在父表中引用的键被更新或删除时，自动处理子表中发生的情况。这通过 ON UPDATE 和 ON DELETE 子句完成。

• RESTRICT (默认)：如果存在子行，则阻止更新或删除父行。
• CASCADE：如果父行被更新或删除，相应的子行也会自动更新或删除。
• SET NULL：如果父行被更新或删除，子行中的外键列将被设置为 NULL。这要求外键列是可为空（nullable）的。
• NO ACTION：在 MySQL 中是 RESTRICT 的同义词。

CASCADE 示例

让我们重新定义 orders 表。如果一个客户被删除，其所有订单也应一并删除。

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME NOT NULL,
    customer_id INT, -- 对于 ON DELETE SET NULL，此列必须可为空
    amount DECIMAL(10, 2) NOT NULL,
    CONSTRAINT fk_orders_customers_cascade
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

删除外键约束

要删除外键约束，您需要知道它的符号名称。您可以使用 SHOW CREATE TABLE child_table 命令找到此名称。

语法

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

删除约束后，您将能够无错误地删除父表 customers。

主键与外键：对比

属性	主键	外键
目的	唯一标识表中的每条记录。	将一个表中的记录链接到另一个表中的记录。
唯一性	每行必须唯一。	可以包含重复值。
NULL 值	不能包含 NULL 值。	可以包含 NULL 值，除非定义为 NOT NULL。
每表数量	每个表只能有一个主键。	一个表可以有多个外键。

使用外键的最佳实践

• 索引您的外键：MySQL 不会自动为外键创建索引。为它们创建索引对于 JOIN 查询的性能至关重要。CREATE INDEX idx_customer_id ON orders(customer_id);
• 一致的数据类型：确保外键列和引用的主键列具有完全相同的数据类型和属性（例如，INT UNSIGNED）。
• 谨慎选择操作：CASCADE 可能很危险，因为它可能导致大规模删除。请谨慎使用。RESTRICT 是最安全的默认选项。
• 使用命名约定：为约束使用清晰的命名约定（例如，fk_childtable_parenttable）可以使它们更易于管理。

在应用程序代码中实现外键

尽管外键是在数据库中定义的，但您的应用程序代码会与其交互。下面是使用流行编程语言创建带有外键的表的现代、健壮示例。

Node.js (async/await)
Python (mysql-connector)
Java (JDBC with try-with-resources)
PHP (PDO)



#### Node.js 与 `mysql2/promise`
此示例使用 async/await 来编写简洁、现代的异步代码。

```javascript
// 必需: npm install mysql2
const mysql = require('mysql2/promise');

async function setupDatabase() {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'TUTORIALS'
    });

    const createCustomersTable = `
      CREATE TABLE IF NOT EXISTS customers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL
      ) ENGINE=InnoDB;`;

    const createOrdersTable = `
      CREATE TABLE IF NOT EXISTS orders (
        order_id INT AUTO_INCREMENT PRIMARY KEY,
        customer_id INT NOT NULL,
        product VARCHAR(100) NOT NULL,
        CONSTRAINT fk_orders_customers
          FOREIGN KEY (customer_id) REFERENCES customers(id)
          ON DELETE CASCADE
      ) ENGINE=InnoDB;`;

    console.log('正在创建 customers 表...');
    await connection.execute(createCustomersTable);
    console.log('customers 表已创建或已存在。');

    console.log('正在创建带有外键的 orders 表...');
    await connection.execute(createOrdersTable);
    console.log('orders 表已创建或已存在。');

  } catch (error) {
    console.error('数据库设置失败:', error.message);
  } finally {
    if (connection) {
      await connection.end();
      console.log('连接已关闭。');
    }
  }
}

setupDatabase();

Python 与 mysql-connector-python

此示例使用 with 语句来确保资源得到正确管理。

# 必需: pip install mysql-connector-python
import mysql.connector
from mysql.connector import errorcode

def setup_database():
    config = {
        'user': 'root',
        'password': 'password',
        'host': '127.0.0.1',
        'database': 'TUTORIALS'
    }
    try:
        with mysql.connector.connect(**config) as cnx:
            with cnx.cursor() as cursor:
                create_customers = ("CREATE TABLE IF NOT EXISTS customers ("
                                  "  id INT AUTO_INCREMENT PRIMARY KEY, "
                                  "  name VARCHAR(100) NOT NULL"
                                  ") ENGINE=InnoDB")
                cursor.execute(create_customers)
                print("表 'customers' 已创建或已存在。")

                create_orders = ("CREATE TABLE IF NOT EXISTS orders ("
                               "  order_id INT AUTO_INCREMENT PRIMARY KEY, "
                               "  customer_id INT NOT NULL, "
                               "  product VARCHAR(100) NOT NULL, "
                               "  CONSTRAINT fk_orders_customers "
                               "  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE"
                               ") ENGINE=InnoDB")
                cursor.execute(create_orders)
                print("表 'orders'（带有外键）已创建或已存在。")
            cnx.commit()
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("认证错误")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("数据库不存在")
        else:
            print(err)
    else:
        print("数据库设置完成。")

setup_database()

Java 与现代 JDBC

此示例使用 try-with-resources 进行自动资源管理，并使用 PreparedStatement 增强安全性。

// 通过 Maven/Gradle 添加 MySQL Connector/J 依赖
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateForeignKeyExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        String createCustomersSQL = "CREATE TABLE IF NOT EXISTS customers (" +
                                  "  id INT AUTO_INCREMENT PRIMARY KEY," +
                                  "  name VARCHAR(100) NOT NULL" +
                                  ") ENGINE=InnoDB;";

        String createOrdersSQL = "CREATE TABLE IF NOT EXISTS orders (" +
                               "  order_id INT AUTO_INCREMENT PRIMARY KEY," +
                               "  customer_id INT NOT NULL," +
                               "  product VARCHAR(100) NOT NULL," +
                               "  CONSTRAINT fk_orders_customers " +
                               "  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE" +
                               ") ENGINE=InnoDB;";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement()) {

            System.out.println("连接成功。");
            stmt.execute(createCustomersSQL);
            System.out.println("表 'customers' 已创建或已存在。");
            stmt.execute(createOrdersSQL);
            System.out.println("表 'orders'（带有外键）已创建或已存在。");

        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

PHP 与 PDO

此示例使用 PDO，这是 PHP 中数据库访问的现代标准，并附带错误处理。

<?php
$host = '127.0.0.1';
$db   = 'TUTORIALS';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "连接成功。\n";

    $createCustomersTable = "
      CREATE TABLE IF NOT EXISTS customers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL
      ) ENGINE=InnoDB;";
    $pdo->exec($createCustomersTable);
    echo "表 'customers' 已创建或已存在。\n";

    $createOrdersTable = "
      CREATE TABLE IF NOT EXISTS orders (
        order_id INT AUTO_INCREMENT PRIMARY KEY,
        customer_id INT NOT NULL,
        product VARCHAR(100) NOT NULL,
        CONSTRAINT fk_orders_customers
          FOREIGN KEY (customer_id) REFERENCES customers(id)
          ON DELETE CASCADE
      ) ENGINE=InnoDB;";
    $pdo->exec($createOrdersTable);
    echo "表 'orders'（带有外键）已创建或已存在。\n";

} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>