个人学习 | MySQL（表的约束机制）

在关系型数据库中，约束是一种非常重要的机制，它可以用来对数据进行限制和保护。常见的约束方式有列级约束和表级约束。

列级约束

在单个列或字段上定义的约束，用于规定该列的取值范围、规则和限制。例如，在创建一个学生信息表时，其中的年龄字段必须大于0，则可以在该字段后面添加 CHECK 约束来限制（仅在MySQL中可使用CHECK约束），语句如下所示：

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL CHECK (age > 0),
  gender ENUM('male', 'female') NOT NULL
);

在这个例子中，age字段被定义为非空且取值必须大于0，这是一种列级约束。

表级约束

在整张表上定义的约束，用于规定整张表中的数据的取值范围、规则和限制。例如，在创建一个订单信息表时，要求订单总金额必须大于等于0，并且一个订单只能属于一个客户，则可以使用表级约束来完成，语句如下所示：

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  status ENUM('NEW', 'PROCESSING', 'SHIPPED', 'DELIVERED') NOT NULL DEFAULT 'NEW',
  CONSTRAINT check_total_amount CHECK (total >= 0),
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
  UNIQUE (id, customer_id)
);

在这个例子中，使用 CONSTRAINT 关键字来指定了一个表级约束，即检查订单总金额必须大于等于0。另外，还使用了 FOREIGN KEY 来定义外键和 UNIQUE 约束来确保一个订单只能属于一个客户，这些都是表级约束。

总的来说，列级约束和表级约束各自有着不同的作用和适用场景。列级约束通常用于对表格单个列进行约束，而表级约束则用于对整个表进行约束。

非空约束

非空约束用于保证某个字段不为空，如果尝试插入或更新一个空值，则会抛出异常。在创建表时，可以在字段定义后添加关键字 NOT NULL 来指定该字段为非空字段。例如：

CREATE TABLE students (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  age INT
);

在这个例子中，id和name字段都被指定为非空字段，而age字段没有被指定，所以它可以为空。

唯一约束

唯一约束用于保证某个字段的唯一性。如果尝试插入或更新一个已经存在的值，则会抛出异常。在创建表时，可以在字段定义后添加关键字 UNIQUE 来指定该字段为唯一字段。例如：

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) UNIQUE,
  email VARCHAR(100)
);

在这个例子中，name字段被指定为唯一字段，因此每个学生的名字必须是唯一的，email字段没有被指定为唯一字段，因此允许多个学生使用同一个电子邮件地址。

主键约束

主键约束用于唯一标识表中的每一条记录，每个表只能有一个主键，通常使用自增长整数类型的字段作为主键。在创建表时，可以通过 PRIMARY KEY 关键字来指定主键，例如：

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  age INT
);

在这个例子中，id被指定为主键，并且使用 AUTO_INCREMENT 指令来实现自增长特性。

外键约束

外键约束用于建立两个表之间的关联关系，保证关联数据的一致性。例如，在学生表中加入一个班级id字段，用于关联班级表中的记录。可以通过 FOREIGN KEY 关键字来指定外键，例如：

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  age INT,
  class_id INT,
  FOREIGN KEY (class_id) REFERENCES classes(id)
);

在这个例子中，class_id被指定为外键，并且使用 REFERENCES 关键字来指定它关联的是班级表中的id字段。

级联操作

当定义外键约束后，MySQL还提供了多种级联操作方式，用于在删除或更新主表时自动进行关联表的相关操作。常见的级联操作包括：

• CASCADE：级联删除或更新
• SET NULL：将外键字段设置为NULL
• RESTRICT：禁止删除或更新操作

例如，在上面的学生表和班级表的例子中，如果我们将级联操作设为CASCADE，那么删除班级表中的一条记录时，会自动删除所有相关的学生记录，代码如下：

CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  age INT,
  class_id INT,
  FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
);

表设计示例

以下是一个包含所有提到的约束的表设计示例：

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  status ENUM('NEW', 'PROCESSING', 'SHIPPED', 'DELIVERED') NOT NULL DEFAULT 'NEW',
  UNIQUE (customer_id, order_date),
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
);

CREATE TABLE order_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  phone VARCHAR(20),
  address VARCHAR(200)
);

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0
);

在这个例子中，orders表包含了非空约束、主键约束、唯一约束和外键约束；order_items表包含了非空约束、主键约束和外键约束；customers表包含了非空约束、主键约束和唯一约束；products表包含了非空约束和主键约束。这些约束可以保证数据的完整性和一致性，提高了数据可靠性和安全性。