2.8k 字 35 分钟

本文最后更新于：4 个月前

什么是约束

约束：constraint
MySQL中的约束，实则是对数据表中数据的限制条件

约束的作用

约束保证数据的完整性和一致性

  • 比如：设计数据表时，要求username字段不允许重复，这时候就需要在CREATE数据表时加入相应的约束。再比如注册时候必须填写邮箱、性别等。

约束的分类

按约束定义的位置可分

  • 列级约束
  • 表级约束

按约束的作用可分

主键 约束 PRIMARY KEY
外键 约束 FOREIGN KEY
非空 约束 NOT NULL
唯一 约束 UNIQUE KEY
默认 约束 DEFAULT

列级约束、表级约束

区别

  1. 列级约束：只能应用于一列上。
    表级约束：可以应用于一列上，也可以应用在一个表中的多个列上。

    即：如果你创建的约束涉及到该表的多个属性列，则必须创建的是表级约束（必须定义在表级上）；否则既可以定义在列级上也可以定义在表级上此时只是SQL语句格式不同而已。

  2. 列级约束：包含在列定义中，直接跟在该列的其它定义之后 ，用空格分隔；不必指定列名。
    表级约束：与列定义相互独立，不包含在列定义中；与定义用‘，’分隔；必须指出要约束的列的名称。

    注：因为在创建列级约束时，只需将创建列约束的语句添加到该字段（列）的定义子句后面；而在创建表级约束时，需要将创建表级约束的语句添加到各个字段（列）定义语句的后面，因为并不是每个定义的字段都要创建约束，所以必须指明需要创建的约束的列名。

举例说明

Create Table project(
项目编号 INT CONSTRAINT pk_pno PRIMARY kEY，
项目名称 CHAR(20)，
项目负责人 CHAR(20)，
CONSTRAINT un_pname_pm UNIQUE(项目名称，项目负责人)
);

分析：“项目编号”字段设置为主键，主键约束名为pk_pno，此主键约束为列主键约束。“项目名称”和“项目负责人”的组合字段设置唯一性约束，此约束为表级约束）

具体说明

一：既可以创建列级约束又可以创建表级约束的：

1、主键约束（primary key constraint）：
（1）定义列约束：CONSTRAINT <约束名> PRIMARY KEY
（2）定义表约束：[CONSTRAINT <约束名>] PRIMARY KEY( <列名> [{<列名>}])

2、唯一性约束（unique key constraint）：
（1）定义列约束：[CONSTRAINT <约束名>] UNIQUE [KEY]
（2）定义表约束：CONSTRAINT <约束名> UNIQUE [KEY] ( <列名> [{<列名>}])

3、外键约束（foreign key constraint）：
定义 列级约束：[CONSTRAINT <约束名>] FOREIGN KEY REFERENCES <外表名>
定义表级约束：[CONSTRAINT <约束名>] FOREIGN KEY REFERENCES <外表名>( <列名> [{<列名>}])

二：只能创建列级约束的：

1，缺省约束（default constraint）：
[CONSTRAINT <约束名>] DEFAULT 约束条件

2，NULL 约束：
[CONSTRAINT <约束名>] [NULL | NOT NULL]

主键约束 PRIMARY KEY

“主键（PRIMARY KEY）”的完整称呼是“主键约束”。MySQL 主键约束是一个列或者列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可以强制表的实体完整性。

涉及术语

  • 主键约束
  • 主键字段
  • 主键值

三种术语的关系：

表中的某个字段添加主键约束后，该字段为主键字段，主键字段中出现的每一个数据都称为主键值

特点

  • 保证数据的实体完整性

要求

一张表应该有主键字段，如果没有，表示该表无效

  • 主键值：是当前行数据的唯一标识、是当前行数据的身份证号
  • 即使表中两行记录相关数据相同，但由于主键值不同，所以也认为是两行不同的记录

按主键约束的字段数量分类

在MySQL的一个表中只有唯一的一个主键，不能有多列主键，但可以有复合主键。一个表中可以：单列做主键、多列做主键（复合主键）。

  • 单一主键：给一个字段添加主键约束
  • 复合主键：给多个字段联合添加一个主键约束(只能用表级定义)

单一主键（列级定义）

mysql> create table t_user(
    -> id int(10) primary key,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.07 sec)

单一主键（表级定义）

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> constraint t_user_id_pk primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

复合主键（表级定义）

一定要在表级定义。

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128) unique,
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.05 sec)

配合AUTO_INCREMENT

在MySQL数据库提供了一个自增的数字，专门用来自动生成主键值，主键值不用用户维护，自动生成，自增数从1开始，以1递增(auto_increment)

mysql> create table t_user(
    -> id int(10) primary key auto_increment,
    -> name varchar(32) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

/*插入两行记录，id主键值会自动增加*/
mysql> insert into t_user(name) values('jay');
Query OK, 1 row affected (0.04 sec)
mysql> insert into t_user(name) values('man');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | jay  |
|  2 | man  |
+----+------+
2 rows in set (0.00 sec)

外键约束 FOREIGN KEY

外键约束（FOREIGN KEY）用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。

外键的主要作用是保持数据的一致性、完整性。例如，部门表 tb_dept 的主键是 id，在员工表 tb_emp5 中有一个键 deptId 与这个 id 关联。

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

快速理解

之前创建表的时候都是在一张表中添加记录，比如如下表：

公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费。这个时候，解决方法：我们完全可以定义一个部门表，然后让员工信息表关联该表，如何关联，即foreign key。

我们可以将上表改为如下结构：

涉及到的术语

  • 外键约束
  • 外键字段
  • 外键值

三种术语的关系：

某个字段添加外键约束之后，该字段称为外键字段，外键字段中每个数据都是外键值

特点

  • 保持数据的一致性
  • 完整性
  • 实现一对一或者一对多关系

这也是为什么称为MySQL关系型数据库的原因

要求

  1. ★父表（子表所参照的表）和子表（具有外键列的表）必须使用使用相同的存储引擎，而且禁止使用临时表。
  2. ★外键对应的是参照完整性，一个表的外键可以为NULL，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。
  3. ★定义外键时，创建先创建父表。定义外键后，插入先插入父表数据，同理，删除先删除子表数据。
  4. 数据表的存储引擎只能为InnoDB。（编辑数据库的默认存储引擎，修改完需要重启MySQL服务）
  5. 外键列（加FOREIGN KEY关键词的一列）和参照列（外键列参照的一列）必须具有相似的数据类型,如果是数字数据类型，则数字长度或者是否有符号位必须相同；如果为字符数据类型长度可以不同。
  6. ★外键列和参照列（主键在创建的同时，会自动创建索引seq_in_index）必须创建索引。如果外键列不存在索引的话，MySQL将自动创建。

与主键的区别

一张表可以有多个外键，但是主键最多只有一个。

按外键约束的字段数量分类

  • 单一外键：给一个字段添加外键约束

  • 复合外键：给多个字段联合添加一个外键约束

表复合外键（表级定义）

FOREIGN KEY(classno) REFERENCES t_class(cno);

非空约束

用NOT NULL约束的字段不能为NULL值，必须给定具体的数据。

注意的是

NULL，表示空，并不是字符串中的空。两者需要区分开。

实例

mysql> CREATE TABLE t12(id INT NOT NULL);  #设置字段id不为空
Query OK, 0 rows affected (0.03 sec)

mysql> DESC t12;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
row in set (0.01 sec)

验证

mysql> INSERT INTO t12 VALUES();  #不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value

唯一约束

UNIQUE约束的字段，具有唯一性，不可重复，但可以为NULL。

列级约束

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128) unique
    -> );
Query OK, 0 rows affected (0.03 sec)

表级约束

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32) not null,
    -> email varchar(128），
    -> unique(email)
    -> );

如果插入相同email会报错

mysql> insert into t_user(id,name,email) values(1,'xlj','932834897@qq.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id,name,email) values(2,'jay','932834897@qq.com');
ERROR 1062 (23000): Duplicate entry '932834897@qq.com' for key 'email'

扩展：联合唯一约束

# 创建services表
mysql> create table services(
        id int,
        ip char(15),
        port int,
        unique(id),
        unique(ip,port)
       );
Query OK, 0 rows affected (0.05 sec)

mysql> desc services;
+-------+----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id        | int(11)   | YES   | UNI  | NULL       |             |
| ip        | char(15) | YES   | MUL  | NULL       |             |
| port    | int(11) | YES   |          | NULL       |             |
+-------+----------+------+-----+---------+-------+
rows in set (0.01 sec)

#联合唯一，只要两列记录，有一列不同，既符合联合唯一的约束
mysql> insert into services values
       (1,'192,168,11,23',80),
       (2,'192,168,11,23',81),
       (3,'192,168,11,25',80);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from services;
+------+---------------+------+
| id   | ip            | port |
+------+---------------+------+
|    1 | 192,168,11,23 |   80 |
|    2 | 192,168,11,23 |   81 |
|    3 | 192,168,11,25 |   80 |
+------+---------------+------+
rows in set (0.00 sec)

mysql> insert into services values (4,'192,168,11,23',80);
ERROR 1062 (23000): Duplicate entry '192,168,11,23-80' for key 'ip'

更多可参考 mysql 给表添加唯一约束、联合唯一约束，指定唯一约束的名字

默认约束

为某一字段提供默认值。

创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

实例

create table tb1(
    id int not null,
    sex char defalut 'b'
);

验证

mysql> insert into tb1(id) values(1); #给t11表插一个空的值
Query OK, 1 row affected (0.00 sec)

#查询结果如下
mysql> select * from t11;
+------+------+
|  id  |  sex |
+------+------+
|  1   |   b  |
+------+------+
row in set (0.00 sec)

主要文献：
[部分概念来自C语言中文网] http://c.biancheng.net/view/2440.html
[大部分案例的代码块来自CSDN] https://blog.csdn.net/w_linux/article/details/79655073

注：案例中的SQL语句并非作者写的，存在规范问题，但不影响阅读。

 目录