SQL 外键Foreign Key全解析

SQL 外键(Foreign Key)全解析

一、外键的基本概念

1.1 什么是外键？

外键是数据库表中用于建立和加强两个表之间联系的一列或多列组合。它指向另一个表的主键，确保数据的参照完整性。

1.2 核心作用

• 维护数据一致性：确保子表中的数据必须在父表中存在
• 建立表间关系：实现一对多、多对一等关系
• 防止孤立数据：避免"孤儿记录"（没有对应父记录的子记录）

二、外键语法详解

2.1 创建表时定义外键

-- 基本语法
CREATE TABLE 子表名 (
    列1 数据类型,
    列2 数据类型,
    ...
    FOREIGN KEY (子表列名) REFERENCES 父表名(父表列名)
    [ON DELETE 删除规则]
    [ON UPDATE 更新规则]
);

-- 示例：订单表引用客户表
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

2.2 为已存在表添加外键

ALTER TABLE 子表名
ADD CONSTRAINT 约束名
FOREIGN KEY (列名) REFERENCES 父表名(列名);

-- 示例
ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerOrder
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

三、外键约束规则

3.1 ON DELETE 规则

规则	说明	示例
CASCADE	删除父记录时，自动删除所有相关子记录	删除客户时，其所有订单也被删除
SET NULL	删除父记录时，子表中外键设为NULL	删除客户时，订单的CustomerID设为NULL
SET DEFAULT	删除父记录时，子表中外键设为默认值	删除客户时，CustomerID设为默认值(如0)
RESTRICT / NO ACTION	阻止删除有子记录的父记录（默认）	有订单的客户不能被删除

3.2 ON UPDATE 规则

规则	说明
CASCADE	父表主键更新时，自动更新所有相关子记录外键
SET NULL	父表主键更新时，子表中外键设为NULL
SET DEFAULT	父表主键更新时，子表中外键设为默认值
RESTRICT	阻止更新有子记录的父记录主键

3.3 示例代码

-- 使用级联删除
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) 
    REFERENCES Customers(CustomerID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- 使用SET NULL
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) 
    REFERENCES Customers(CustomerID)
    ON DELETE SET NULL
    ON UPDATE SET NULL
);

四、外键与表关系

4.1 一对多关系（最常用）

-- 部门表（一）
CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

-- 员工表（多）
CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(100),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

4.2 多对多关系（使用中间表）

-- 学生表
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100)
);

-- 课程表
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100)
);

-- 中间表（连接表）
CREATE TABLE StudentCourses (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

4.3 自引用关系

-- 员工层级结构
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    ManagerID INT,  -- 指向同一表的其他员工
    FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

五、外键的最佳实践

5.1 命名规范

-- 推荐命名方式：FK_子表名_父表名_列名
ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers_CustomerID
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

5.2 索引优化

-- 为外键列创建索引（通常数据库会自动创建）
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);

5.3 检查外键关系

-- 查看所有外键约束（MySQL）
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = '数据库名'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

-- 查看所有外键约束（SQL Server）
EXEC sp_fkeys @pktable_name = 'Customers';

六、常见问题与解决方案

6.1 违反外键约束错误

-- 错误：尝试插入不存在的CustomerID
INSERT INTO Orders (OrderID, CustomerID) 
VALUES (1001, 9999);  -- 如果CustomerID=9999不存在，会报错

-- 解决方案：先确保父记录存在
INSERT INTO Customers (CustomerID, CustomerName) 
VALUES (9999, '新客户');

INSERT INTO Orders (OrderID, CustomerID) 
VALUES (1001, 9999);

6.2 删除有外键引用的记录

-- 错误：尝试删除有订单的客户
DELETE FROM Customers WHERE CustomerID = 1;

-- 解决方案1：先删除子记录
DELETE FROM Orders WHERE CustomerID = 1;
DELETE FROM Customers WHERE CustomerID = 1;

-- 解决方案2：使用级联删除（需要在定义外键时设置）

6.3 临时禁用外键约束

-- MySQL
SET FOREIGN_KEY_CHECKS = 0;
-- 执行批量操作...
SET FOREIGN_KEY_CHECKS = 1;

-- SQL Server
ALTER TABLE Orders NOCHECK CONSTRAINT FK_CustomerOrder;
-- 执行操作...
ALTER TABLE Orders CHECK CONSTRAINT FK_CustomerOrder;

-- PostgreSQL
ALTER TABLE Orders DISABLE TRIGGER ALL;
-- 执行操作...
ALTER TABLE Orders ENABLE TRIGGER ALL;

七、外键的性能考虑

7.1 优点

• 自动维护数据完整性
• 提供明确的文档说明表间关系
• 优化器可以利用外键信息优化查询计划

7.2 缺点

• 插入/更新/删除操作需要检查约束，有一定性能开销
• 批量操作可能变慢
• 可能造成死锁（在复杂的关系网中）

7.3 性能优化建议

-- 1. 在非高峰时段执行批量操作
-- 2. 使用事务减少锁竞争
BEGIN TRANSACTION;

-- 批量操作...

COMMIT;

-- 3. 考虑暂时禁用外键约束（生产环境谨慎使用）

八、不同数据库的外键实现差异

特性	MySQL	PostgreSQL	SQL Server	Oracle
自动创建索引	是	是	是	否
延迟约束检查	不支持	支持	有限支持	支持
级联更新	支持	支持	支持	支持
禁用所有约束	SET FOREIGN_KEY_CHECKS	表级DISABLE TRIGGER	NOCHECK CONSTRAINT	DISABLE CONSTRAINT

九、实战示例：完整的电子商务系统

-- 1. 创建主表
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerName VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(200) NOT NULL,
    Price DECIMAL(10,2) CHECK(Price > 0)
);

-- 2. 创建订单相关表
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerID INT NOT NULL,
    OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Status VARCHAR(20) DEFAULT 'Pending',
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
    INDEX IX_Orders_CustomerID (CustomerID),
    INDEX IX_Orders_OrderDate (OrderDate)
);

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY AUTO_INCREMENT,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL CHECK(Quantity > 0),
    UnitPrice DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
    ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
    ON DELETE RESTRICT,
    UNIQUE KEY UQ_Order_Product (OrderID, ProductID)
);

-- 3. 插入测试数据
INSERT INTO Customers (CustomerName, Email) 
VALUES 
    ('张三', 'zhangsan@example.com'),
    ('李四', 'lisi@example.com');

INSERT INTO Products (ProductName, Price)
VALUES 
    ('笔记本电脑', 5999.00),
    ('智能手机', 2999.00);

-- 4. 插入订单数据（会自动检查外键约束）
INSERT INTO Orders (CustomerID) 
VALUES (1);  -- CustomerID=1必须存在

INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice)
VALUES 
    (1, 1, 1, 5999.00),  -- OrderID=1和ProductID=1必须存在
    (1, 2, 2, 2999.00);

-- 5. 查询关联数据
SELECT 
    o.OrderID,
    c.CustomerName,
    p.ProductName,
    od.Quantity,
    od.UnitPrice,
    od.Quantity * od.UnitPrice AS Total
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID;

十、总结

外键使用建议：

何时使用外键：

• 需要确保数据完整性的关键业务表
• 表之间存在明确的父子关系
• 需要数据库自动维护关系完整性

何时避免使用外键：

• 频繁的大批量数据导入
• 需要极高写入性能的场景
• 分布式数据库环境（跨库外键难以实现）
• 使用应用程序层维护数据完整性

设计原则：

• 保持外键命名一致且有意义
• 合理选择ON DELETE/UPDATE规则
• 考虑性能影响，为外键列创建索引
• 定期检查外键约束的有效性

外键是数据库设计中非常重要的工具，正确使用可以显著提高数据质量和系统可靠性，但需要根据具体业务场景权衡使用。