Mysql系列基础篇-约束

约束

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性

修改列的名称和类型
alter table 表名 change 列名 新列名 新数据类型;
alter table 表名 modify 列名 新数据类型;

普通索引

alter table 表名 index 索引名（字段名）；

多列索引

alter table 表名 index 索引名（字段名1，字段名2，字段名3）；

非空约束

非空约束：not null

• 创建表的时候添加
• 修改列的方式添加

添加唯一约束
alter table student modify name varchar(32)  not null;
删除唯一约束
alter table student modify name varchar(32);

唯一约束

唯一约束：unique

• 创建表的时候添加
• 修改列的方式添加

添加唯一约束
alter table student modify varchar(32) unique;
删除唯一约束
alter table student drop index 索引名;

主键约束

主键约束：primary key
含义：非空且唯一
一张表只能有一个字段为主键
主键就是表中记录的唯一标识

• 创建表的时候添加

 create table 表名（
 id int primary key ,  --给id添加主键约束
 ...
 ）;

• 修改列的方式添加

添加主键索引
alter table student monify id int primary ;
删除主键约束
alter table student drop primary key;

自动增长
概念：如果某一列是数值类型的，使用auto_increment可以来完成值的自动增长,一般和主键一起使用

• 创建表的时候添加

 create table 表名（
 id int primary key auto_increment,  --给id添加主键约束且设置自增
 ...
 ）;

• 修改列的方式添加

添加自动增长
alter table student modify id int auto_increment;
删除自动增长（主键这种方式无法删除）
alter table student modify id int;

外键约束

外键约束：foreign key
语法：
create table 表名(
…
外键列,
constraint 外键名
foreign key （外键列名称）
references 主表名称（主表列名称）
);

• 创建表的时候添加

create table dep(
id int primary key auto_increment,
dep_name varchar(20),
dep_address varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
constraint emp_dep_fk 
foreign key(dep_id) 
references  dep(id)
);

• 修改列的方式添加

增加外键
alter table emp 
add 
constraint emp_dep_fk 
foreign key(dep_id) 
references  dep(id);
删除外键
alter table emp drop foreign key emp_dep_fk;

级联操作

案例：需要跟新dep的部门id，emp表中有外键约束dep_id

mysql> select * from emp;
+----+----------+---------+--------+
| id | emp_name | emp_age | dep_id |
+----+----------+---------+--------+
|  1 | 小二     |      22 |      1 |
|  2 | 小三     |      25 |      2 |
|  3 | 小四     |      27 |      3 |
|  5 | 小五     |      32 |      1 |
+----+----------+---------+--------+
mysql> select * from dep;
+----+-----------+-------------+
| id | dep_name  | dep_address |
+----+-----------+-------------+
|  1 | 研发部    | 北京        |
|  2 | 运维部    | 天津        |
|  3 | 测试部    | 上海        |
+----+-----------+-------------+
更新dep中的部门id报错
mysql> update dep set id=5 where dep_name='研发部'; 
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`practice`.`emp`, CONSTRAINT `dep_emp_fk` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
删除外键
alter table emp drop foreign key dep_emp_fk;
添加外键设置级联更新
alter table emp 
add 
constraint dep_emp_fk 
foreign key(dep_id) 
references dep(id) 
on update cascade;
再次更新dep的部门id
update dep set id=5 where dep_name='研发部';
跟新后的emp和dep的表内容
mysql> select * from emp;
+----+----------+---------+--------+
| id | emp_name | emp_age | dep_id |
+----+----------+---------+--------+
|  1 | 小二     |      22 |      5 |
|  2 | 小三     |      25 |      2 |
|  3 | 小四     |      27 |      3 |
|  5 | 小五     |      32 |      5 |
+----+----------+---------+--------+
mysql> select * from dep;
+----+-----------+-------------+
| id | dep_name  | dep_address |
+----+-----------+-------------+
|  2 | 运维部    | 天津        |
|  3 | 测试部    | 上海        |
|  5 | 研发部    | 北京        |
+----+-----------+-------------+

级联更新

添加外键设置级联更新
alter table emp 
add 
constraint emp_dep_fk 
foreign key(dep_id) 
references  dep(id)
on update cascade;

案例：删除dep某个部门的同时，emp中对应部门的所有员工均被删除

mysql> select * from emp;
+----+----------+---------+--------+
| id | emp_name | emp_age | dep_id |
+----+----------+---------+--------+
|  1 | 小二     |      22 |      1 |
|  2 | 小三     |      25 |      2 |
|  3 | 小四     |      27 |      3 |
|  5 | 小五     |      32 |      1 |
+----+----------+---------+--------+
mysql> select * from dep;
+----+-----------+-------------+
| id | dep_name  | dep_address |
+----+-----------+-------------+
|  1 | 研发部    | 北京        |
|  2 | 运维部    | 天津        |
|  3 | 测试部    | 上海        |
+----+-----------+-------------+
删除外键索引 
alter table emp drop foreign key dep_emp_fk;

添加外键设置级联更新、级联删除
alter table emp 
add 
constraint dep_emp_fk 
foreign key(dep_id) 
references dep(id) 
on update cascade 
on delete cascade;

删除dep中id为1的部门
mysql> delete from dep where id=1;

mysql> select * from dep;
+----+-----------+-------------+
| id | dep_name  | dep_address |
+----+-----------+-------------+
|  2 | 运维部    | 天津        |
|  3 | 测试部    | 上海        |
+----+-----------+-------------+

mysql> select * from emp;
+----+----------+---------+--------+
| id | emp_name | emp_age | dep_id |
+----+----------+---------+--------+
|  2 | 小三     |      25 |      2 |
|  3 | 小四     |      27 |      3 |
+----+----------+---------+--------+

级联删除

alter table emp 
add 
constraint dep_emp_fk 
foreign key(dep_id) 
references dep(id) 
on update cascade 
on delete cascade;(级联更新和级联删除也可以单独使用)