mysql约束

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`))