MySQL 表约束超详细

在MySQL里，“约束”指的是对表中数据的一种限制约束，它能够确保数据库中数据的准确性和有效性

比如有的数据是必填项，就像身份认证的时候，或者填注册信息的时候，手机号身份证这种就不能空着，所以就有了非空约束；又有的数据比如用户的唯一id，不能跟其他人的一样，所以就需要使用唯一约束等等。

在MySQL中主要有6种约束：主键约束、外键约束、唯一约束、检查约束、非空约束和默认值约束。

一. 主键约束——PRIMARY KEY

“主键约束“是6种约束中使用最多的约束。

在建立数据表的时候，一般情况下，为了方便更快地查找表中的记录，都会要求在表中设置一个“主键”。”主键“是表里面的一个特殊字段，这个字段能够唯一标识该表中的每条信息。

主键分为“单字段主键”和“多字段联合主键”，并且在使用主键的时候需要注意以下几个点：

• 一个表只能定义一个主键；
• 主键值必须唯一标识表中的每一行，并且不能出现null的情况，即表中不能存在有相同主键的两行或两行以上数据，严格遵守唯一性原则；
• 一个字段名只能在联合主键字段表中出现一次；
• 联合主键不能包含不必要的多余字段，以满足最小化原则。

1.在建表时设置主键约束

设置单字段主键

1）在定义字段的时候设置主键约束，语法格式：

<字段名> <数据类型> PRIMARY KEY [默认值]

例：在数据库中创建学生信息数据表st_info，主键为st_id, SQL语句以及运行结果如下：

create table st_info(
st_id int(10) primary key,
  name varchar(20),
  class varchar(10),
  gender varchar(4),
  age int(2)
);

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | YES |     | NULL |       |
| class | varchar(10) | YES | | NULL   | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

2）在定义完所有字段之后指定部件，语法格式：

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

例 在数据库中创建学生信息数据表

st_info2

,主键为

st_id

, SQL语句以及运行结果如下：

create table st_info2(
st_id int(10),
  name varchar(20),
  class varchar(10),
  gender varchar(4),
  age int(2),
   primary key(st_id)
);

mysql> desc st_info2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | YES |     | NULL |       |
| class | varchar(10) | YES | | NULL   | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

设置联合主键

联合主键即一张表的主键由多个字段组成。

比如在学生信息表中，可以设置st_id跟name来作为联合主键，语法格式以及SQL语句实现为：

PRIMARY KEY [字段1，字段2，…,字段n]

create table st_info3(
  st_id int(10),
  name varchar(20),
  class varchar(10),
  gender varchar(4),
  age int(2),
   primary key(st_id,name)
);

mysql> desc st_info3;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL |       |
| class | varchar(10) | YES | | NULL   | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

需要注意的是，在设置联合主键的时候，不能在每个字段名后面直接声明主键约束。

2. 在修改表的时候添加主键约束

如果在创建表的时候没有设置主键约束，还可以在修改表时进行添加，但是设置成主键约束的字段不允许有空值。语法格式：

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

mysql> desc st_info4;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class | varchar(10) | YES | | NULL   | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+

将

st_id

与

name

设置成主键：

mysql> alter table st_info4 add primary key(st_id,name);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0  Warnings: 0

mysql> desc st_info4;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL |       |
| class | varchar(10) | YES | | NULL   | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3. 删除主键约束

当一个表中不需要主键约束时，就需要从表中将其删除。

例 删除

st_info4

数据表中的主键约束：

mysql> alter table st_info4 drop primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0  Warnings: 0

mysql> desc st_info4;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class | varchar(10) | YES | | NULL   | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

4. 【补充】 MySQL设置主键自增长 ：

在MySQL里，当主键定义为自增长后，主键的值就不需要自己再输入数据了，而是由数据库系统根据定义自动赋值，每增加一条记录，主键就会自动根据设置的步长进行增长。

在MySQL中自增长的关键字是

AUTO_INCREMENT

，语法格式为：

字段名 数据类型 AUTO_INCREMENT

例 创建学生信息表

st_info5

，指定

st_id

字段自增，

create table st_info5(
  st_id int(10) primary key auto_increment,
    name varchar(20) not null,
    class varchar(10),
    gender varchar(4),
    age int(2)
);

此时可以不用再手动插入

st_id

的数据：

insert into st_info5(name,class,gender,age) 
values('王思瑶','管理学1班','女',18),
      ('王绮梦','管理学1班','女',18),
      ('李若彤','管理学1班','男',18);
      
mysql> select * from st_info5;
+-------+--------+-----------+--------+------+
| st_id | name | class     | gender | age |
+-------+--------+-----------+--------+------+
| 1 | 王思瑶 | 管理学1班 | 女 | 18 |
| 2 | 王绮梦 | 管理学1班 | 女 | 18 |
| 3 | 李若彤 | 管理学1班 | 男 | 18 |
+-------+--------+-----------+--------+------+
3 rows in set (0.01 sec)

但是在设置学号的时候不一定是从1开始的，比如要从2000150001开始，则需要在建表之后设置自增的起始数据：

create table st_info6(
    st_id int(10) primary key auto_increment,
    name varchar(20) not null,
    class varchar(10),
    gender varchar(4),
    age int(2)
)auto_increment=2000150001;

insert into st_info6(name,class,gender,age) 
values('王思瑶','管理学1班','女',18),
      ('王绮梦','管理学1班','女',18),
      ('李若彤','管理学1班','男',18);
      
mysql> select * from st_info6;
+------------+--------+-----------+--------+------+
| st_id | name | class     | gender | age |
+------------+--------+-----------+--------+------+
| 2000150001 | 王思瑶 | 管理学1班 | 女 | 18 |
| 2000150002 | 王绮梦 | 管理学1班 | 女 | 18 |
| 2000150003 | 李若彤 | 管理学1班 | 男 | 18 |
+------------+--------+-----------+--------+------+
3 rows in set (0.00 sec)

值得注意的地方是，如果在插入某一行数据产生了报错，则最终自增字段可能会出现不连续的情况。

二. 外键约束——FOREIGN KEY

外键约束是表的一个特殊字段，普遍会和主键约束一起使用，用来确保数据的一致性。

对于两个具有关联关系的表来说，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

所以外键就是用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

在定义外键时需要遵守以下规则：

• 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称做自参照表，这种结构称做自参照完整性；
• 必须为主表定义主键；
• 主键不能包含空值，但允许在外键中出现空值；
• 在主表的表名后面指定列名或列名的组合，这个列或列的组合必须是主表的主键或候选键；
• 外键中列的数目必须和主表的主键中列的数目相同；
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

1.在创建表时设置外键约束

在建表语句中，可以加入关键字

FOREIGN KEY

来指定外键，用

REFERENCES

来连接与主表的关系语法格式：

CONSTRAINT <约束名>
FOREIGN KEY <外键名>(字段名1，字段名2...)
REFERENCES <主表名>(主键字段名)

例：

① 使用1部分创建的

st_info5

数据表作为主表：

mysql> desc st_info5;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| st_id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO |     | NULL |                |
| class  | varchar(10) | YES  | | NULL    | |
| gender | varchar(4) | YES |     | NULL |                |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+

② 创建

tb_1

数据表，并在表上创建外键约束，使其中

course_id

作为外键关联到表

st_info5

的主键

st_id

：

create table tb_1(
    course_id int(8) not null auto_increment,
    course_name varchar(25) not null,
    constraint course_Choosing
    foreign key fk_course(course_id)
    references st_info5(st_id)
)auto_increment=20015001;

mysql> desc tb_1;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| course_id | int | NO | MUL | NULL | auto_increment |
| course_name | varchar(25) | NO |     | NULL |                |
+-------------+-------------+------+-----+---------+----------------+

上面语句执行成功之后，在表

tb_1

中添加了

course_Choosing

的约束名称，以及外键名称为

fk_course

的

course_id

字段，依赖于表

st_info5

的主键

st_id

。

2. 在修改表时添加外键约束

同样的可以在创建表之后再修改，SQL语句如下：

alter table tb_1 
add 
constraint course_Choosing
foreign key fk_course(course_id)
references st_info5(st_id);

这里需要注意的是，从表的外键关联的必须是主表的主键，且主键和外键的数据类型必须一致。例如两者都是int型或者都是char型数据。

3. 删除外键约束

当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系。

删除的语法格式：

ALTER TABLE <表名> 
DROP 
FOREIGN KEY <外键约束名>;

例：

alter table tb_1
drop
foreign key fk_course;

三. 唯一约束——UNIQUE KEY

唯一约束就是指所有记录中字段的值不能重复出现，比如给'id'字段加上唯一约束之后，每条记录的id值都是唯一的，不能出现重复的情况。

唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的，虽然只能有一个空值。

例如，在用户信息表中，要避免表中的用户名重名，就可以把用户名列设置为唯一约束。

1.在创建表时设置唯一约束

唯一约束可以在创建表的时候直接进行设置，通常设置在除了主键以外的其他列上，语法格式如下：

<字段名> <数据类型> UNIQUE

例 创建图书馆所有书本明细表book_info ，设定书本bk_id为主键，书本ISBN为唯一键，SQL语句如下：

create table book_info(
    bk_id int primary key,
    ISBN varchar(17) unique,
    author varchar(50),
    Publisher varchar(20)
);

mysql> desc book_info;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| bk_id | int | NO | PRI | NULL | |
| ISBN | varchar(17) | YES | UNI | NULL |       |
| author | varchar(50) | YES | | NULL | |
| Publisher | varchar(20) | YES |     | NULL |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2. 在修改表时添加唯一约束

语法格式：

ALTER TABLE <数据表名> 
ADD 
CONSTRAINT <唯一约束名> UNIQUE(<列名>);

**例 **将book_info表中author字段设置为唯一约束：

alter table book_info
add
constraint uni_author unique(author);

mysql> desc book_info;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| bk_id | int | NO | PRI | NULL | |
| ISBN | varchar(17) | YES | UNI | NULL |       |
| author | varchar(50) | YES | UNI | NULL | |
| Publisher | varchar(20) | YES |     | NULL |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

3. 删除唯一约束

语法格式：

ALTER TABLE <表名> 
DROP INDEX <唯一约束名>;

例 删除book_info表中的唯一约束uni_author；

alter table book_info
drop index uni_author;

mysql> desc book_info;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| bk_id | int | NO | PRI | NULL | |
| ISBN | varchar(17) | YES | UNI | NULL |       |
| author | varchar(50) | YES | | NULL | |
| Publisher | varchar(20) | YES |     | NULL |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

四. 检查约束——CHECK

检查约束是用来检查数据表中字段值有效性的一种约束。

例如，学生信息表中的年龄字段是不能有负数的，并且数值范围也是有限制的。如果是大学生，正常年龄一般应该在 18~30 岁之间。在设置字段的检查约束时需要根据实际情况进行设置，这样能够减少无效数据的输入。

1.在创建表时设置检查约束

语法格式：

CHECK(<约束条件>)

例 在persons表创建时id_p列创建check约束，约束条件为id_p列数据必须只包含大于0的整数：

create table persons(
    id_p int not null,
    lastname varchar(255) not null,
    firstname varchar(255),
    address varchar(255),
    city varchar(255),
    check (id_p>0)
);

mysql> desc persons;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id_p | int | NO | | NULL | |
| lastname | varchar(255) | NO |     | NULL |       |
| firstname | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES |     | NULL |       |
| city | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+

2. 在修改表时添加检查约束

语法格式：

ALTER TABLE <表名> 
ADD 
CONSTRAINT <检查约束名> CHECK(<检查约束>)

例 修改persons数据表，要求city字段为“上海”：

create table persons(
    id_p int not null,
    lastname varchar(255) not null,
    firstname varchar(255),
    address varchar(255),
    city varchar(255),
    constraint chk_person check (id_p>0 and city='sandnes')
);

alter table persons 
add 
constraint chk_ct check(city='上海');

mysql> desc persons;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id_p | int | NO | | NULL | |
| lastname | varchar(255) | NO |     | NULL |       |
| firstname | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES |     | NULL |       |
| city | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3. 删除检查约束

语法格式：

ALTER TABLE <表名> 
DROP CONSTRAINT <检查约束名>;

例 删除persons表中的check_ct约束：

alter table persons 
drop constraint chk_ct;

五. 非空约束——NOT NULL

非空约束是用来约束表中的字段不能为空。在表中某个列的定义后加上关键字NOT NULL作为限定词，来约束该列的取值不能为空。

例如，在学生信息表中，如果不添加学生姓名，那么这条记录是无效的，这种情况下就可以为用户名字段设置非空约束。

1.在创建表时设置非空约束

语法格式：

CREATE TABLE <表名>(
    ...
<字段名> <数据类型> NOT NULL)
...
);

**例 **创建学生信息表st_info, 指定st_id为主键，学生姓名不能为空：

create table st_info(
    st_id int(10) primary key,
    name varchar(20) not null,
    class varchar(10),
    gender varchar(4),
    age int(2)
);

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class  | varchar(10) | YES  | | NULL    | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

2. 在修改表时添加非空约束：

如果在创建表的时候忘记了为字段设置非空约束，可以通过修改表进行非空约束的添加。

语法格式：

ALTER TABLE <表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> NOT NULL;

例 修改学生信息表st_info中class字段，设置成非空约束：

mysql> alter table st_info
    -> change column class
    -> class varchar(20) not null;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class  | varchar(20) | NO   | | NULL    | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3. 删除非空约束

可以在修改语句中对非空约束进行删除。

语法格式：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> 
<字段名> <数据类型> NULL;

例 修改学生信息表st_info，将class字段的非空约束删除：

mysql> alter table st_info
    -> change column class
    -> class varchar(20) null;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class  | varchar(20) | YES  | | NULL    | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

六. 默认值约束——DEFAULT

默认值约束是当数据表中某个字段不输入值的时候，自动为其添加一个已经设置好的值。

例如，在注册学生信息时，如果不输入学生的性别，那么会默认设置一个性别或者输入一个“未知”。

默认值约束一般是用在已经设置了非空约束的列，这样能够防止数据表在录入数据时出现错误。

1.在创建表时设置默认值约束

语法格式：

CREATE TABLE <表名>(
    ...
<字段名> <数据类型> DEFAULT <默认值>
    ...
);

例 创建学生信息表st_info，设置年龄age默认值为18：

create table st_info(
    st_id int(10) primary key,
    name varchar(20) not null,
    class varchar(10),
    gender varchar(4),
    age int(2) default 18
);

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class  | varchar(10) | YES  | | NULL    | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | 18      | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

2. 在修改表时添加默认值约束

语法格式：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> 
<字段名> <数据类型> DEFAULT <默认值>;

例 修改学生信息表st_info，将性别设置默认值为“保密”：

mysql> alter table st_info
    -> change column gender
    -> gender varchar(4) default '保密';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class  | varchar(10) | YES  | | NULL    | |
| gender | varchar(4) | YES |     | 保密 |       |
| age | int | YES | | 18      | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

需要注意的是，这里的gender字段为字符串型数据，需要加上引号。

3. 删除默认值

删除默认值即为将默认值设置为空（null）

语法格式：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> 
<字段名> <数据类型> DEFAULT NULL;

例 修改学生信息表st_info, 将gender性别字段的默认值删除掉：

mysql> alter table st_info
    -> change column gender
    -> gender varchar(4) default null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO |     | NULL |       |
| class  | varchar(10) | YES  | | NULL    | |
| gender | varchar(4) | YES |     | NULL |       |
| age | int | YES | | 18      | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

以上6个约束都很重要，并且有的可以联合使用，并不是都是单独使用的。作为基础知识点，会让你的数据库更加规范，从而提高工作效率。