Mysql中的约束（常见约束、外键约束）

csdn推荐

约束的定义

约束就是对于数据库的表中字段，在某些性质上进行约束，以规范化字段或者实现一些功能。

常见的约束

首先我们先创建一个用于存储员工和所对应公司的数据库。

mysql> create database employee_company;
Query OK, 1 row affected (0.01 sec)
mysql> use employee_company;
Database changed

创建公司的table

mysql> create table company(
    -> Id int primary key auto_increment unique,
    --公司的id作为主键（primary key），
    --是公司的唯一标识，并且子自增（auto_increment），
    --唯一（unique）。
    -> name varchar(30) not null,
    --公司名称不能为空（not null）。
    -> catagory varchar(30) not null default '私企');
    --公司类型不能为空（not null），
    --默认是私企（default '私企'）。
Query OK, 0 rows affected (0.04 sec)
mysql> desc company;--表创建成功
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| Id       | int         | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30) | NO   |     | NULL    |                |
| catagory | varchar(30) | NO   |     | 私企    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

如果想在已经创建好的table上修改字段的类型或者约束可以如下操作，将name字段改成varchar(50)并且唯一。

语法：alter table 表名称 modify 字段名 新类型 新约束;

mysql> alter table company modify name varchar(50) unique;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc company;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| Id       | int         | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50) | YES  | UNI | NULL    |                |
| catagory | varchar(30) | NO   |     | 私企    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

再往公司table中加入数据

mysql> insert into company values(1,'腾讯','互联网企业'),(2,'国家电网','国企'),(3,'中电十四所','研究所');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from company;
+----+------------+------------+
| Id | name       | catagory   |
+----+------------+------------+
|  1 | 腾讯       | 互联网企业 |
|  2 | 国家电网   | 国企       |
|  3 | 中电十四所 | 研究所     |
+----+------------+------------+
3 rows in set (0.01 sec)

外键约束

创建员工table，并与公司table中的Id字段相关联

关联的语法格式为：

constrain 外键名称 foreign key(作为外键的字段) references 主表(主表字段));

作为另一个表的外键的这个字段不一定是主键，但必须具有唯一性，并且不能为空，还要和关联字段的类型一致。

mysql> create table employee(
    -> Id int primary key auto_increment,
    -> name varchar(50) not null,
    -> gender varchar(10),
    -> com_Id int,
    -> constraint fk_com_emp foreign key(com_Id) references company(Id));
    --com_Id字段作为外键关联到company(Id)
Query OK, 0 rows affected (0.03 sec)
mysql> desc employee;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| Id     | int         | NO   | PRI | NULL    | auto_increment |
| name   | varchar(50) | NO   |     | NULL    |                |
| gender | varchar(10)  | YES  |     | NULL    |                |
| com_Id | int         | YES  | MUL | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

向employee中插入数值

mysql> delete from employee where name = '张三';
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(1,'张三','男',1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(2,'李四','男',1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(3,'王二麻子','女',3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee values(4,'路人甲','女',2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from employee;
+----+----------+--------+--------+
| Id | name     | gender | com_Id |
+----+----------+--------+--------+
|  1 | 张三     | 男     |      1 |
|  2 | 李四     | 男     |      1 |
|  3 | 王二麻子 | 女     |      3 |
|  4 | 路人甲   | 女     |      2 |
+----+----------+--------+--------+
4 rows in set (0.00 sec)

外键约束

此时删除employee中的一条数据

是可以成功删除的

mysql> delete from employee where id=1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from employee;
+----+----------+--------+--------+
| Id | name     | gender | com_Id |
+----+----------+--------+--------+
|  2 | 李四     | 男     |      1 |
|  3 | 王二麻子 | 女     |      3 |
|  4 | 路人甲   | 女     |      2 |
+----+----------+--------+--------+
3 rows in set (0.00 sec)

但是再尝试删除employee中的一条数据

就会报错：不能删除或更新父表的行：外键约束失败 ，说明关联了从表的主表中的字段是不能删除的。

mysql> delete from company where name = '腾讯';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`employee_company`.`employee`, CONSTRAINT `fk_com_emp` FOREIGN KEY (`com_Id`) REFERENCES `company` (`Id`))

通过多表查询查出两个关联表构成的一个整体的表

mysql> select * from company,employee where employee.com_Id = company.Id;
+----+------------+------------+----+----------+--------+--------+------+
| Id | name       | catagory   | Id | name     | gender | com_Id | age  |
+----+------------+------------+----+----------+--------+--------+------+
|  1 | 腾讯       | 互联网企业 |  2 | 李四     | 男     |      1 | NULL |
|  2 | 国家电网   | 国企       |  4 | 路人甲   | 女     |      2 | NULL |
|  3 | 中电十四所 | 研究所     |  3 | 王二麻子 | 女     |      3 | NULL |
+----+------------+------------+----+----------+--------+--------+------+

限制字段的范围

语法格式：

alter table 表名 add constraint modify check(字段名称 in(可选的值1，可选的值2，…));

如：限制性别选项

mysql> alter table employee add constraint check(gender in('男','女'));
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

当然也可以限制int类型数值的区间，比如年龄

mysql> alter table employee add age int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table employee add constraint check(age between 20 and 65);
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

文章来源:https://blog.csdn.net/weixin_43836337/article/details/139216755