一篇文章带你搞懂：MySQL数据库表约束（非空约束、主键约束、唯一约束、外键约束、自增约束、默认值约束、检查约束）

---

数据库表约束的全面指南

在数据库设计中，约束是确保数据完整性和准确性的关键工具。本文将详细介绍如何在MySQL数据库中实现各种类型的约束，包括非空、主键、唯一、检查、外键、自增和默认值约束。我们将通过创建和修改表的示例来展示这些约束的应用，并提供详细的代码注释和表格结果。

1. 非空约束

非空约束确保列不能包含NULL值。这对于确保数据库中的数据完整性至关重要。

创建表语句：

CREATE TABLE fruits(
  f_id CHAR(10) NOT NULL, -- 定义一个长度为10的字符型字段，不允许为空
  s_id INT NOT NULL, -- 定义一个整数字段，不允许为空
  f_name CHAR(255) NULL, -- 定义一个长度为255的字符型字段，允许为空
  f_price DECIMAL(8,2) -- 定义一个带有两位小数的十进制字段
);

查看表结构：

DESC fruits;

结果：

Field	Type	Null	Key	Default
f_id	char(10)	NO
s_id	int	NO
f_name	char(255)	YES
f_price	decimal(8,2)	YES

2. 主键约束

主键约束用于标识表中的唯一记录。一个表可以有一个或多个列作为主键。

(1) 列级主键约束：

CREATE TABLE fruits(
  f_id CHAR(10) NOT NULL PRIMARY KEY, -- 定义为主键，不允许为空，且值必须唯一
  s_id INT NOT NULL,
  f_name CHAR(255) NULL,
  f_price DECIMAL(8,2)
);

(2) 表级主键约束：

CREATE TABLE fruits(
  f_id CHAR(10) NOT NULL,
  s_id INT NOT NULL,
  f_name CHAR(255) NOT NULL,
  f_price DECIMAL(8,2),
  PRIMARY KEY(s_id, f_name) -- 定义复合主键，由s_id和f_name两个字段组成
);

查看表结构：

DESC fruits;

结果：

Field	Type	Null	Key
f_id	char(10)	NO
s_id	int	NO	PRI
f_name	char(255)	NO	PRI
f_price	decimal(8,2)	YES

3. 唯一约束

唯一约束确保列中的所有值都是唯一的，但可以包含NULL。

创建唯一约束：

CREATE TABLE suppliers(
  s_id INT NOT NULL PRIMARY KEY,
  s_name CHAR(50) NOT NULL,
  s_city CHAR(50) NULL,
  s_zip CHAR(10) NULL,
  s_call CHAR(50) NOT NULL UNIQUE -- 定义为唯一，不允许有重复的值
);

查看表结构：

DESC suppliers;

结果：

Field	Type	Null	Key
s_id	int	NO	PRI
s_name	char(50)	NO
s_city	char(50)	YES
s_zip	char(10)	YES
s_call	char(50)	NO	UNI

4. 检查约束

检查约束用于限制列中的值必须满足的条件。

创建检查约束：

CREATE TABLE employee(
  eno DECIMAL(2) PRIMARY KEY,
  ename VARCHAR(8),
  age DECIMAL(3) CONSTRAINT age_CK CHECK (age > 20 AND age < 60), -- 限制年龄在20到60之间
  deptno DECIMAL(2),
  address VARCHAR(30)
);

查看表结构：

DESC employee;

结果：

Field	Type	Null	Key	Default
eno	decimal(2)	NO	PRI
ename	varchar(8)	YES
age	decimal(3)	YES
deptno	decimal(2)	YES
address	varchar(30)	YES

5. 外键约束

外键约束用于在两个表之间建立链接，并确保引用的数据完整性。

创建外键约束：

CREATE TABLE suppliers(
  s_id INT NOT NULL PRIMARY KEY,
  s_name CHAR(50) NOT NULL,
  s_city CHAR(50) NULL,
  s_zip CHAR(10) NULL,
  s_call CHAR(50) NOT NULL
);

CREATE TABLE fruits(
  f_id CHAR(10) NOT NULL PRIMARY KEY,
  s_id INT NOT NULL,
  f_name CHAR(255) NOT NULL,
  f_price DECIMAL(8,2) NOT NULL,
  CONSTRAINT sid_FK FOREIGN KEY(s_id) REFERENCES suppliers(s_id) ON DELETE CASCADE ON UPDATE CASCADE -- 定义外键，引用suppliers表的s_id字段，并设置级联删除和更新
);

查看表结构：

DESC fruits;

结果：

Field	Type	Null	Key	Default	References
f_id	char(10)	NO	PRI
s_id	int	NO			suppliers(s_id)
f_name	char(255)	NO
f_price	decimal(8,2)	NO

6. 自增约束

自增约束用于自动生成唯一的值，通常用于主键。

创建自增约束：

CREATE TABLE suppliers(
  s_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -- 定义为主键，并且设置为自增
  s_name CHAR(50) NOT NULL,
  s_city CHAR(50) NULL
);

插入数据：

INSERT INTO suppliers(s_name, s_city) VALUES ('FastFruit Inc.', 'Tianjin'), ('LT Supplies', 'Chongqing'), ('ACME', 'Shanghai');

查看表数据：

SELECT * FROM suppliers;

结果：

s_id	s_name	s_city
1	FastFruit Inc.	Tianjin
2	LT Supplies	Chongqing
3	ACME	Shanghai

7. 默认值约束

默认值约束用于在插入记录时，如果未指定列的值，则自动填充默认值。

创建默认值约束：

CREATE TABLE orders(
  o_num INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- 定义为主键，并且设置为自增
  o_data DATETIME DEFAULT(CURDATE()), -- 设置默认值为当前日期
  c_id INT
);

插入数据：

INSERT INTO orders(o_num, c_id) VALUES (30001, 10001), (30002, 10003);

查看表数据：

SELECT * FROM orders;

结果：

o_num	o_data	c_id
30001	2024-04-05	10001
30002	2024-04-05	10003

通过这些示例，可以看到如何在MySQL中使用各种约束来确保数据的完整性和准确性。这些约束是数据库设计中不可或缺的部分，有助于维护数据的一致性和可靠性。