MySQL约束
参考了b站的一天学会mysql数据库
链接:
https://blog.csdn.net/weixin_57909453/article/details/119789974
主键约束
它能够唯一确定一张表中的一条记录,不能重复且主键值不能为空
create table user(
id int primary key,
name varchar(20));
insert into user values(1,'张三');
Query OK, 1 row affected (0.01 sec)
insert into user values(1,'张三');
ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'
insert into user values(2,'张三');
Query OK, 1 row affected (0.00 sec)
insert into user values(NULL,'张三');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'values(NULL,'张三')' at line 1
–联合主键:设为主键的任意字段都不能为空
–只要联合的主键加起来不重复就可以
mysql> create table user2(
-> id int,
-> name varchar(20),
-> password varchar(20),
-> primary key(id,name));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into user2 values(1,'张三','123');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user2 values(2,'张三','123');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user2 values(1,'李四','123');
Query OK, 1 row affected (0.00 sec)
自增约束
auto_increment管控主键的值,使其自动补齐
mysql> create table user3
-> (id int primary key auto_increment,
-> name varchar(20));
Query OK, 0 rows affected (0.06 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)
mysql> insert into user3 (name) values('张三');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user3;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 张三 |
+----+--------+
2 rows in set (0.00 sec)
–如果忘记了添加主键约束
mysql> create table user4
-> (id int ,
-> name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
--修改表结构 增加主键约束
mysql> alter table user4 add primary key (id);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
--删除主键约束
mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
--使用modify修改主键约束
mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
唯一约束
–确保字段的值不重复
mysql> create table user5
-> (id int,
-> name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into user5 values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user5 values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'user5.name'
--在创建表时直接创建唯一约束
create table user6
(id int ,
name varchar(20),
unique (name));
create table user7
(id int ,
name varchar(20) unique);
create table user8
(id int ,
name varchar(20),
unique (id,name));
mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
--联合唯一约束,两个键联合起来不重复就行
mysql> insert into user8 values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user8 values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry '1-zhangsan' for key 'user8.id'
mysql> insert into user8 values(2,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user8 values(1,'lisi');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user8;
+------+----------+
| id | name |
+------+----------+
| 1 | lisi |
| 1 | zhangsan |
| 2 | zhangsan |
+------+----------+
3 rows in set (0.00 sec)
--如何删除唯一约束
mysql> alter table user7 drop index name;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
--modify添加唯一约束
mysql> alter table user7 modify name varchar(20) unique;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
–总结
1.建表的时候添加约束
2.可以使用alter…add…添加约束
3.可以使用alter…modify…添加约束
4.可以使用alter…drop…删除约束
非空约束
mysql> create table user9
-> (id int,
-> name varchar(20) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into user9 values(1,'张三');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user9 values(1,NULL);
ERROR 1048 (23000): Column 'name' cannot be null
默认约束
–当我们添加字段时,如果没有传值时,就会使用默认值
mysql> create table user10
-> (id int,
-> name varchar(20),
-> age int default 10);
Query OK, 0 rows affected (0.03 sec)
mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | 10 | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into user10 (id,name) values(1,'张三');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user10;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | 张三 | 10 |
+------+--------+------+
1 row in set (0.00 sec)
--传了值就不会使用默认值
mysql> insert into user10 values(2,'李四',20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user10;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | 张三 | 10 |
| 2 | 李四 | 20 |
+------+--------+------+
2 rows in set (0.00 sec)
外键约束
–涉及到两个表:父表、子表
–主表、副表
–班级
create table classes
(id int primary key,
name varchar(20));
–学生表
create table students
(id int primary key,
name varchar(20),
class_id int,
foreign key(class_id) references classes(id));
insert into classes values(1,'一班');
insert into classes values(2,'二班');
insert into classes values(3,'三班');
insert into classes values(4,'四班');
insert into students values(1001,'张三',1);
insert into students values(1002,'张三',2);
insert into students values(1003,'张三',3);
insert into students values(1004,'张三',4);
insert into students values(1005,'李四',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`shop`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
–主表classes中没有的数据在副表中是不可以使用的
–主表中的记录被副表引用,是不可以被删除的
delete from classes where id=4
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`shop`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))