MySQL数据表的常见约束小结_Mysql

在数据库设计中，约束（constraints）是用于确保数据的完整性、准确性和一致性的规则。mysql 提供了多种约束类型，帮助我们规范数据存储，并确保其符合预期的规则。常见的约束包括主键（primary key）、外键（foreign key）、唯一约束（unique）、非空约束（not null）、默认值（default）和检查约束（check）。

1. 主键约束（primary key）

作用：主键是用于唯一标识表中每一行数据的列。它不仅要求该列的值是唯一的，而且不能为空（即不允许 null）。每个表只能有一个主键。

特点：

唯一性：主键列中的每个值必须是唯一的。
不允许空值：主键列不能包含 null 值。
自动索引：mysql 会自动为主键列创建索引，优化查询性能。

示例：

create table employees (
    employee_id int not null,
    first_name varchar(50),
    last_name varchar(50),
    primary key (employee_id)
);

在上述示例中，employee_id 是主键，它唯一标识每个员工记录，并且不能为 null。

2. 外键约束（foreign key）

作用：外键约束用于在两张表之间建立连接关系，确保一个表中的某列数据必须与另一表中的主键或唯一键数据相匹配。外键用于维护数据的参照完整性。

特点：

外键列的值必须与参照表中的主键或唯一列值一致。
外键约束可以定义更新或删除时的级联操作，例如：on delete cascade 或 on update cascade。

示例：

create table departments (
    department_id int not null,
    department_name varchar(50),
    primary key (department_id)
);

create table employees (
    employee_id int not null,
    first_name varchar(50),
    last_name varchar(50),
    department_id int,
    primary key (employee_id),
    foreign key (department_id) references departments(department_id)
);

在这个例子中，employees 表中的 department_id 列是外键，指向 departments 表中的 department_id 列。这确保了每个员工必须隶属于某个部门。

3. 唯一约束（unique）

作用：唯一约束确保某一列中的所有值都是唯一的，不允许重复。与主键不同的是，唯一约束允许列中存在 null 值，但多个 null 值可以存在。

特点：

保证列中所有非 null 的值是唯一的。
允许多个 null 值（在不同数据库系统中可能有所不同）。

示例：

create table users (
    user_id int not null,
    username varchar(50) unique,
    email varchar(100) unique,
    primary key (user_id)
);

在上面的表中，username 和 email 列具有唯一约束，确保没有两个用户使用相同的用户名或邮箱。

4. 非空约束（not null）

作用：非空约束确保列中的数据不能为 null。当定义了 not null 约束时，插入数据时必须为该列提供一个有效的值。

特点：

强制要求列必须有值。
常用于对业务逻辑有特殊要求的字段。

示例：

create table products (
    product_id int not null,
    product_name varchar(100) not null,
    price decimal(10, 2),
    primary key (product_id)
);

在该示例中，product_name 列不能为 null，每个产品必须有名称。

5. 默认值约束（default）

作用：默认值约束用于在插入数据时为列指定一个默认值。当插入数据时没有提供该列的值时，mysql 会使用默认值。

特点：

提供一个列的默认值。
如果未指定列的值，数据库将使用默认值。

示例：

create table orders (
    order_id int not null,
    order_date date default current_date,
    status varchar(20) default 'pending',
    primary key (order_id)
);

在这个例子中，order_date 列的默认值是当前日期，而 status 列的默认值是 ‘pending’。

6. 检查约束（check）

作用：检查约束用于限制列中的数据必须满足某个条件。它可以确保插入的数据符合特定规则。此约束在 mysql 8.0.16 及之后版本中得到了支持。

特点：

强制列中的数据符合指定的条件。
可以使用逻辑表达式定义复杂的条件。

示例：

create table employees (
    employee_id int not null,
    first_name varchar(50),
    last_name varchar(50),
    age int,
    salary decimal(10, 2),
    primary key (employee_id),
    check (age >= 18),
    check (salary >= 0)
);

在该表中，age 列的值必须大于等于 18，salary 列的值必须大于等于 0。

7. 逻辑与物理连接性

在数据库中，逻辑连接性和物理连接性是数据库设计中重要的概念：

逻辑连接性指的是通过外键约束在表之间建立的关联关系。这些关联定义了数据如何在逻辑上被组织和约束。

物理连接性侧重于数据库表在物理存储上的关联。例如，数据库可以使用索引来提高查询效率，尤其是在主键和唯一约束下，mysql 会为这些列自动创建索引。

示例：

create table products (
    product_id int not null,
    product_name varchar(100),
    primary key (product_id)
);

create table order_items (
    order_item_id int not null,
    product_id int not null,
    quantity int,
    primary key (order_item_id),
    foreign key (product_id) references products(product_id)
);

在上述示例中，order_items 表通过 product_id 外键与 products 表建立了逻辑上的连接。物理上，mysql 可能会为 product_id 列创建索引，以加速查询。

8. 修改约束（alter table）

如果在创建表后，需要修改表的约束，可以使用 alter table 语句来添加、删除或修改约束。

示例：添加约束

alter table employees
add constraint chk_age check (age >= 18 and age <= 65);

示例：删除约束

alter table employees
drop constraint chk_age;

9. 综合示例：创建完整的订单系统

结合前面的约束，以下是一个简单的订单系统设计示例，其中包含主键、外键、唯一约束、非空约束、默认值和检查约束：

create table customers (
    customer_id int not null,
    name varchar(100) not null,
    email varchar(100) unique,
    phone varchar(15),
    primary key (customer_id)
);

create table products (
    product_id int not null,
    product_name varchar(100) not null,
    price decimal(10, 2) not null,
    primary key (product_id)
);

create table orders (
    order_id int not null,
    customer_id int not null,
    order_date date default current_date,
    status varchar(20) default 'pending',
    primary key (order_id),
    foreign key (customer_id) references customers(customer_id)
);

create table order_items (
    order_item_id int not null,
    order_id int not null,
    product_id int not null,
    quantity int check (quantity > 0),
    primary key (order_item_id),
    foreign key (order_id) references orders(order_id),
    foreign key (product_id) references products(product_id)
);

在这个系统中：

customers 表存储客户信息，email 列具有唯一约束，确保每个客户的邮箱地址唯一。
products 表存储商品信息，每个商品有唯一的 product_id。
orders 表记录客户订单，customer_id 是外键，指向 customers 表。
order_items 表记录每个订单的商品，包含外键 order_id 和 product_id，并对 quantity 列设置了检查约束，确保每个订单项的商品数量大于 0。