Mysql从删库到跑路---2Mysql的约束

2021-11-08 15:28:06

一，约束·

1，主键约束
我们可以给数据表的一个数据表添加约束，让它能够唯一确实一个数据表，这个数据被约束后不重复且不唯一：primary key

mysql> use test;
mysql> create table number(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> describe number;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

可以注意到key下面变成了PRI说明他是主键元素。
当我们添加重复的id就会报错。

mysql> insert into number value(1,'张三');
Query OK, 1 row affected (0.03 sec)

mysql> insert into number value(1,'张三');
ERROR 1062 (23000): Duplicate entry '1' for key 'number.PRIMARY'

只有我们的id改了不重复才能添加另外一个张三

mysql> select * from number;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 张三 |
+----+------+
2 rows in set (0.01 sec)

除此之外，主键约束的数据也不能为空

mysql> insert into number value(null,'张三');
ERROR 1048 (23000): Column 'id' cannot be null

2，主键约束-联合主键
在最后用primary key()定义或者在每一个变量后定义

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

mysql> desc number2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into number2 value(1,'张三');
Query OK, 1 row affected (0.02 sec)

mysql> insert into number2 value(2,'李四');
Query OK, 1 row affected (0.01 sec)

mysql> select *from number2;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
+----+------+

如果再添加一条id name都一样的数据就会报错

mysql> insert into number2 value(2,'李四');
ERROR 1062 (23000): Duplicate entry '2-李四' for key 'number2.PRIMARY'

但如果id和name有一个不一样就可以了

mysql> insert into number2 value(1,'李五');
Query OK, 1 row affected (0.02 sec)

mysql> select *from number2;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  1 | 李五 |
|  2 | 李四 |
+----+------+
3 rows in set (0.00 sec)

当然，id和name都不能为空

mysql> insert into number2 value(null,'李四');
ERROR 1048 (23000): Column 'id' cannot be null

mysql> insert into number2 value(2,null);
ERROR 1048 (23000): Column 'name' cannot be null

3，自增约束
在数据后面加上auto_increment就可以使id自加而不用用户自己传

mysql> create table user3(
    -> id int primary key auto_increment,
    -> name varchar(15)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> desc user3
    -> ;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(15) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into user3 (name) values('张三');
Query OK, 1 row affected (0.02 sec)

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

可以看出，我们并没有传id进去，而是主动帮我们补全

mysql> insert into user3 (name) values('李四');
Query OK, 1 row affected (0.03 sec)

mysql> select * from user3;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
+----+------+
2 rows in set (0.00 sec)

当我们再传入一个数据之后，id就会进行自增

4，如果我们在建立数据表的时候忘记添加主键约束，我们还可以使用：1）alter table (数据表名称) add (约束) (要约束的数据)
2）使用modify：alter table （数据表名称） modify （要约束的数据）（数据类型+数据约束类型）

mysql> create table use4(
    -> id int,
    -> name varchar(15)
    -> );
Query OK, 0 rows affected (0.07 sec)

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

在上面我们并没有对id进行任何的约束
1）当我们为id使用alter…add后，可以看出id已经被添加主键

mysql> alter table use4 add primary key(id);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

2）当我们为id使用modify后，可以看出id也已经被添加主键

mysql> alter table use4  modify id int primary key;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

5，当我们想要删除主键时，可以使用：
alter table (数据表名称) drop (约束)

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

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

6，唯一约束 unqiue
key数值为uni就是表示数据不能重复！

mysql> alter table use4 add unique(name);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

这样我们就可以给id和name都添加唯一约束，这样当添加新的数据的时候id和name有一个相同都不行，与主键联合约束不一样！

mysql> insert into use4 value(1,'张三');
Query OK, 1 row affected (0.02 sec)

mysql> insert into use4 value(2,'李四');
Query OK, 1 row affected (0.02 sec)

mysql> select * from use4;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into use4 value(1,'李五');
ERROR 1062 (23000): Duplicate entry '1' for key 'use4.PRIMARY'
mysql> insert into use4 value(3,'李五');
Query OK, 1 row affected (0.02 sec)

7,删除唯一约束主键:alter table (数据表名称) drop index (数据名);

mysql> alter table use4 drop index id;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

8，非空约束
在数据表建立的时候，在数据后添加not null就可以
作用就是，数据不能为空。

9，默认约束
简单理解就是给数据设定一个默认值，当你没有传入数据的时候，默认使用设定的默认值。

mysql> create table use5(
    -> id int,
    -> name varchar(15),
    -> age int default 10
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> desc use5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(15) | YES  |     | NULL    |       |
| age   | int         | YES  |     | 10      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into use5 (id,name) value(1,'张三');
Query OK, 1 row affected (0.02 sec)

mysql> insert into use5 value(2,'李四','15');
Query OK, 1 row affected (0.02 sec)

mysql> select * from use5;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 张三 |   10 |
|    2 | 李四 |   15 |
+------+------+------+
2 rows in set (0.01 sec)

可以看出，张三的age我们没有填写就默认为10，而李四的age我们填写了就是我们填写的数据。

重点：10外键约束

外键约束一般设计两个表以上：父表和子表

父表：

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

子表：

mysql> create table students(
    -> id int primary key,
    -> class_id int,
    -> foreign key(class_id) references classes(id)
    -> );
Query OK, 0 rows affected (0.08 sec)

父表添加数据：

mysql> insert into classes values(1,'一班');
Query OK, 1 row affected (0.01 sec)

mysql> insert into classes values(2,'二班');
Query OK, 1 row affected (0.02 sec)

mysql> insert into classes values(3,'三班');
Query OK, 1 row affected (0.02 sec)

mysql> insert into classes values(4,'四班');
Query OK, 1 row affected (0.02 sec)

mysql> select *from classes;
+----+------+
| id | name |
+----+------+
|  1 | 一班 |
|  2 | 二班 |
|  3 | 三班 |
|  4 | 四班 |
+----+------+
4 rows in set (0.01 sec)

子表添加数据：

mysql> insert into students values(1001,1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into students values(1002,2);
Query OK, 1 row affected (0.02 sec)

mysql> insert into students values(1003,3);
Query OK, 1 row affected (0.02 sec)

mysql> insert into students values(1004,4);
Query OK, 1 row affected (0.02 sec)

mysql> select *from students;
+------+----------+
| id   | class_id |
+------+----------+
| 1001 |        1 |
| 1002 |        2 |
| 1003 |        3 |
| 1004 |        4 |
+------+----------+
4 rows in set (0.00 sec)

注意：1，主表classes没有的数据值，在子表中不可以使用。

mysql> insert into students values(1005,5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

这里因为主表中没有5班这个数值，当子表想添加5班就会报错
2，主表中的记录被子表引用时不能被删除

mysql> delete from classes where id=4;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

码农公寓

一，约束·

重点：10外键约束

相关文章