MySQL从入门到精通(四)约束

约束（constraint）简介

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

约束分类

• 非空约束（not null）
• 默认约束 （default）
• 唯一性约束（unique）
• 主键约束（primary key）PK
• 外键约束（foreign key）FK
• 检查约束 (目前MySQL不支持、Oracle支持)

约束的使用

1、非空约束

非空约束指字段的值不能为空。

mysql> create table tb_emp6(
    -> id int(11) not null,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> desc tb_emp6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

对于使用了非空约束的字段，如果用在添加数据时没有指定值，数据库系统会报错

mysql> insert into tb_emp6 (name) values("张三");
ERROR 1364 (HY000): Field 'id' doesn't have a default value

在创建后的表中添加约束

 alter table <表名>  modify <列名> <数据类型> not null;

删除非空约束

alter table <表名> modify <列名> <数据类型>

2、默认约束

默认约束指定字段的默认值，如果添加的数据没为字段指定值，那么系统会自动的给该字段指定默认值

mysql> create table tb_emp8(
    -> id int(11),
    -> name varchar(32),
    -> age int(11) default 18
    -> );

mysql> insert into tb_emp8 (id,name)  values (1,"张三");
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_emp8;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 张三 |   18 |
+------+------+------+
1 row in set (0.00 sec)

在创建后的表中添加默认约束

 alter table <表名>  modify <列名> <数据类型> default <默认值>;

删除默认约束

 alter table <表名>  modify <列名> <数据类型>

3、唯一性约束

唯一性约束(Uniqe Constraint )要求该列唯一,允许为空但是只能出现一个空值。唯一性约束可以确保一列后者几列不出现重复值

列级约束

mysql> create table tb_empl9(
    -> id int(11) unique,  //约束添加在列后
    -> name varchar(32)
    -> );

表级约束

mysql> create table tb_empl9(
    -> id int(11),
    -> name varchar(32),
    -> unique(id) //约束添加在创建表后
    -> );

插入id相同数据报错

mysql> insert into tb_empl9 values(1,"张三");
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb_empl9 values(1,"李四");
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

给多个字段联合约束

mysql> create table tb_empl10(
    -> id int(11),
    -> name varchar(32),
    -> unique(id,name)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into tb_empl10  values(1,"张三");
Query OK, 1 row affected (0.01 sec)

//插入与第一条id相同的数据，没问题
mysql> insert into tb_empl10  values(1,"李四");
Query OK, 1 row affected (0.01 sec)

//插入与第一条name相同的数据，没问题
mysql> insert into tb_empl10  values(2,"张三");
Query OK, 1 row affected (0.01 sec)

//插入与第一条name和key都报错的数据，这时候就会报错
mysql> insert into tb_empl10  values(1,"张三");
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'id'
mysql>

删除唯一性约束

alter table 表名 drop index 唯一约束名;

4、主键约束

主键约束（Primary key Constraint）要求主键列的数据唯一，并且不允许为空，是这条数据的唯一标识。

单字段主键

① 列级主键

mysql> create table tb_empl11(
    -> id int(11) primary key,
    -> name varchar(32)
    -> );

② 表级主键

mysql> create table tb_empl12(
    -> id int(11),
    -> name varchar(32),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.10 sec)

多字段联合主键

mysql> create table tb_empl13(
    -> id int(11),
    -> name varchar(32),
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.09 sec)

alter添加主键

alter table <表名> add primary key(字段名)

alter table <表名> modify <字段名> <数据类型> primary key

alter table  add constraint <主键名> primary key(字段名)

删除主键约束

alter table <表名> drop primary key;

5、外键约束

外键是表（从表）中的一个字段，对应另一个表（主表）中的主键。
外键用来在两个表的数据之间建立连接，外键的作用是保持数据的一致性、完整性。

例如：部门表tb_dept的主键是id，在员工表tb_empl中有一个deptId与这个id关联，这就是外键约束。

定义外键后，不允许删除在主表中具有关联关系的行

创建外键约束

//在从表中定义
[constraint <外键名>] foreign key <字段名>  references <主表名> <主键列> 

相关定义：

①、主表(父表)：对于两个具有关联关系的表，相关字段中主键虽在的那个表即是主表

②、从表(子表)：对于两个具有关联关系的表，外键所在的表

③、外键名：外键约束的名称

④、字段名：字表需要添加外键约束的字段名

⑤、主表名：被字表外键所依赖的表名

⑥、主键列：主表中定义的主键，或者列组合

例子：

//主表
mysql> create table tb_dept(
    -> id int(11) primary key,
    -> name varchar(22) not null,
    -> location varchar(50)
    -> );
Query OK, 0 rows affected (0.09 sec)

//从表
mysql> create table tb_empl(
    -> id int(11) primary key,
    -> name varchar(25),
    -> deptId int(11),
    -> salary float,
    -> constraint fk_emp_dept foreign key(deptId) references tb_dept(id)
    -> );
Query OK, 0 rows affected (0.09 sec)

如果删除主表（tb_dept）

mysql> drop table tb_dept;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

如果想顺利删除主表，有两种方法:
1、先删除从表，再删除主表
2、删除从表的外键约束

创建表后添加外键约束

ALTER TABLE <从表名> ADD CONSTRAINT <外键名> FOREIGN KEY(从表字段名) REFERENCES <主表名>(主表主键列)

删除外键约束

alter table 表名 drop foreign key 外键名;

参考：https://blog.youkuaiyun.com/w_linux/article/details/79655073