MySQL 数据库约束类型与外键操作

在 MySQL 数据库中，约束用于限定表中字段的数据，以保证数据的完整性和一致性。

1. 非空约束（NOT NULL）

  • 定义：限制该字段的数据不能为 NULL，即该字段必须有值。

  • 示例：

    CREATE TABLE Students (
  StudentID INT PRIMARY KEY AUTO_INCREMENT,
  Name VARCHAR(50) NOT NULL  -- 非空约束
);

2. 唯一约束（UNIQUE）

  • 定义：确保该字段的所有数据都是唯一的、不重复的。

  • 示例：

    CREATE TABLE Students (
  StudentID INT PRIMARY KEY AUTO_INCREMENT,
  Email VARCHAR(100) UNIQUE  -- 唯一约束
);

3. 主键约束（PRIMARY KEY）

  • 定义：主键是一个表中每行数据的唯一标识，要求非空且唯一。MySQL 中使用 AUTO_INCREMENT 来自动递增主键。

  • 示例：

    CREATE TABLE Students (
  StudentID INT PRIMARY KEY AUTO_INCREMENT,  -- 主键约束 + 自增
  Name VARCHAR(50)
);

4. 默认约束（DEFAULT）

  • 定义：为字段设置默认值，如果插入数据时未指定该字段的值，则自动采用默认值。

  • 示例：

    CREATE TABLE Students (
  StudentID INT PRIMARY KEY AUTO_INCREMENT,
  Major VARCHAR(50) DEFAULT 'Undeclared'  -- 默认值约束
);

5. 检查约束（CHECK）

  • 定义：确保字段值满足某一个条件，例如限制年龄字段为非负数。
  • 注意：MySQL 8.0.16 及更高版本支持 CHECK 约束，之前的版本会忽略该约束。

  • 示例：

    CREATE TABLE Students (
  StudentID INT PRIMARY KEY AUTO_INCREMENT,
  Age INT CHECK (Age >= 0)  -- 检查约束
);

6. 外键约束（FOREIGN KEY）

  • 定义：用于建立两个表之间的连接，确保子表中的值引用主表中的有效值，保持数据的一致性和完整性。

MySQL 中添加和删除外键

在 MySQL 中，可以在创建表时或使用 ALTER TABLE 语句在已有表中添加外键。

在创建表时添加外键

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE
);
  • 解释：
    • FOREIGN KEY (CustomerID)：指定 Orders 表中的 CustomerID 字段为外键。
    • REFERENCES Customers(CustomerID)：外键引用 Customers 表的 CustomerID 字段。
    • ON DELETE CASCADE：父表记录删除时，子表中的相关记录也会被删除。
    • ON UPDATE CASCADE：父表记录更新时，子表中的外键值也会同步更新。

使用 ALTER TABLE 在已有表中添加外键

ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
ON UPDATE CASCADE ON DELETE CASCADE;
  • 解释：
    • ALTER TABLE Orders：指定要修改的表。
    • ADD CONSTRAINT FK_CustomerOrder：添加外键约束并命名为 FK_CustomerOrder。
    • ON UPDATE CASCADE ON DELETE CASCADE：定义删除和更新行为为级联操作。

删除外键

当不再需要外键约束时，可以通过 ALTER TABLE 语句删除外键。MySQL 删除外键时需要使用外键的名称。

ALTER TABLE Orders
DROP FOREIGN KEY FK_CustomerOrder;
  • 解释：此语句将从 Orders 表中删除名为 FK_CustomerOrder 的外键约束。

外键的 ON DELETE 和 ON UPDATE 行为

在 MySQL 中，可以为外键定义 ON DELETE 和 ON UPDATE 行为，用于指定在父表记录被删除或更新时，子表中的外键记录应该如何处理。以下是常见的操作选项：

1. NO ACTION

  • 说明：当在父表中删除或更新对应记录时，先检查是否存在子表中引用该记录的外键。如果存在，则不允许删除或更新。
  • 注意：NO ACTION 与 RESTRICT 实际上效果相同。

2. RESTRICT

  • 说明：与 NO ACTION 类似，当在父表中删除或更新对应记录时，若子表中存在引用该记录的外键，则不允许删除或更新该记录。

  • 示例：

    CREATE TABLE Orders (
  OrderID INT PRIMARY KEY AUTO_INCREMENT,
  CustomerID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE RESTRICT
);

3. CASCADE 级联

  • 说明：当在父表中删除或更新对应记录时，子表中引用该记录的所有外键行也会自动删除或更新。
  • 适用场景：适合父表和子表记录紧密关联的情况，例如级联删除订单及其所有明细。

  • 示例：

    CREATE TABLE Orders (
  OrderID INT PRIMARY KEY AUTO_INCREMENT,
  CustomerID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
);

4. SET NULL

  • 说明：当在父表中删除或更新对应记录时，将子表中该外键字段的值设置为 NULL。
  • 要求：外键字段必须允许 NULL。
  • 适用场景：适合在父表记录被删除或更新后，子表记录仍然需要保留的情况。

  • 示例：

    CREATE TABLE Orders (
  OrderID INT PRIMARY KEY AUTO_INCREMENT,
  CustomerID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE SET NULL
);

5. SET DEFAULT（MySQL 不支持）

  • 说明：父表记录更新或删除后，子表的外键列会被设置成一个默认值。然而，MySQL 的 InnoDB 引擎不支持此选项。

综合示例

-- 创建 Customers 表
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50) NOT NULL
);

-- 创建 Orders 表，并定义外键行为
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    ON DELETE CASCADE         -- 删除父表记录时，子表相应记录自动删除
    ON UPDATE SET NULL         -- 更新父表记录时，将子表外键设为 NULL
);

-- 删除 Orders 表中的外键
ALTER TABLE Orders
DROP FOREIGN KEY FK_CustomerOrder;

行为选择场景

  • NO ACTION / RESTRICT：适用于父子表记录必须一致的场景，防止父表记录被误删或更新。
  • CASCADE：适用于父子记录紧密绑定的场景，如主从关系明确的订单和订单明细。
  • SET NULL：适用于父表记录删除后，子表记录不需要删除但可以解除关系的情况。