SQL主键与外键

SQL主键与外键

引言

主键（Primary Key）和外键（Foreign Key）是关系型数据库中用于维护数据完整性和建立表之间关联的核心机制。主键确保表中每一行的唯一性，而外键则用于在不同表之间建立引用关系。理解这两个概念对于设计高效、可靠的数据库至关重要。

主键（Primary Key）

主键是表中一列或一组列，其值能唯一标识表中的每一行记录。主键具有以下特性：

• 唯一性：主键值在表中必须唯一，不允许重复。
• 非空性：主键列不允许包含NULL值。
• 不可变性：主键值通常不应被修改。

主键的类型

1. 单列主键：由单个列组成的主键。 2. 复合主键：由多个列组合而成的主键。

创建主键的语法

-- 创建表时定义单列主键
CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 创建表时定义复合主键
CREATE TABLE CourseRegistrations (
    student_id INT,
    course_id INT,
    registration_date DATE,
    PRIMARY KEY (student_id, course_id)
);

-- 为已有表添加主键
ALTER TABLE Employees ADD PRIMARY KEY (employee_id);

主键的实际应用

主键常用于：

• 快速查找特定记录
• 作为其他表的外键引用目标
• 确保数据完整性

外键（Foreign Key）

外键是一个表中的字段（或字段集合），它引用另一个表的主键。外键用于：

• 维护参照完整性：确保引用的数据存在
• 建立表间关系：如一对多、多对多关系

外键约束的行为

可以定义外键在引用数据被修改或删除时的行为：

• CASCADE：级联操作（删除或更新）
• SET NULL：将外键设为NULL
• RESTRICT/NO ACTION：阻止操作
• SET DEFAULT：设为默认值

创建外键的语法

-- 创建表时定义外键
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

-- 为已有表添加外键
ALTER TABLE OrderItems
ADD CONSTRAINT fk_order
FOREIGN KEY (order_id) REFERENCES Orders(order_id)
ON DELETE CASCADE;

主键与外键的关系

erDiagram CUSTOMERS ||--o{ ORDERS : "1:N" CUSTOMERS { int customer_id PK string name } ORDERS { int order_id PK int customer_id FK date order_date }

这个ER图展示了：

• Customers表的主键是customer_id
• Orders表通过customer_id外键引用Customers表
• 关系为"一对多"（1个客户可以有多个订单）

实际案例

电子商务数据库示例

-- 创建产品表（主表）
CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2)
);

-- 创建订单表（主表）
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL
);

-- 创建订单项表（包含两个外键）
CREATE TABLE OrderItems (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

操作示例

-- 插入数据
INSERT INTO Products VALUES (1, 'Laptop', 999.99);
INSERT INTO Orders VALUES (101, '2023-01-15');
INSERT INTO OrderItems VALUES (1001, 101, 1, 2);

-- 尝试违反外键约束（会失败）
INSERT INTO OrderItems VALUES (1002, 102, 2, 1); 
-- 错误：order_id 102或product_id 2不存在

-- 级联删除示例
DELETE FROM Orders WHERE order_id = 101;
-- 如果外键定义为ON DELETE CASCADE，OrderItems中相关记录也会被删除

数学表示

在关系代数中，外键约束可以表示为： $\mathrm{\forall }{t}_f\in r_f,\mathrm{\exists }{t}_p\in r_p\text{ 使得 }{t}_f[FK]=t_p[PK]$ 其中：

• $r_f$ 是包含外键的关系
• $r_p$ 是被引用的关系
• $FK$ 是外键属性
• $PK$ 是主键属性

最佳实践

1. 为所有表设计适当的主键 2. 外键应始终引用主键或唯一键 3. 考虑使用有意义的命名约定（如pk_表名、fk_表名_引用表名） 4. 根据业务需求选择适当的外键操作（CASCADE/SET NULL等） 5. 在大数据量情况下注意外键对性能的影响

常见问题

Q：一个表可以有多个外键吗？ A：可以，一个表可以有多个外键，分别引用不同表的主键。

Q：外键必须引用主键吗？ A：不一定，外键可以引用任何被定义为UNIQUE的列，但最佳实践是引用主键。

Q：主键和唯一键有什么区别？ A：主键不允许NULL值且一个表只能有一个主键，而唯一键允许NULL值且可以有多个。