MySQL52-55 唯一性约束/主键约束/外键约束_mysql unique為null可以重複嗎-CSDN博客

本文链接：https://blog.csdn.net/qq_43568982/article/details/114680914

开始时间：2020-03-11

唯一性约束

可以为null但不能重复
添加unique
这样叫列级约束
如下代码所示，在定义时添加unique，此时添加了的字段均不能接受相同数据的赋值

mysql> create table t_user(id int,username varchar(255) unique,usercode varchar(255) unique);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t_user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | YES  |     | NULL    |       |
| username | varchar(255) | YES  | UNI | NULL    |       |
| usercode | varchar(255) | YES  | UNI | NULL    |       |
+----------+--------------+------+-----+---------+-------+

报错

mysql> insert into t_user(id,username,usercode)values(15,'zhangsan','23');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user(id,username,usercode)values(15,'zhangsan','25');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'

mysql> insert into t_user(id,username,usercode)values(15,'zhangn','23');
ERROR 1062 (23000): Duplicate entry '23' for key 'usercode'

注意和以下代码的区别：

多个字段联合约束，这种叫表级约束

mysql> create table t_user2(id int,username varchar(255),usercode varchar(255),
unique(username,usercode));
Query OK, 0 rows affected (0.01 sec)

mysql> desc t_user2;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | YES  |     | NULL    |       |
| username | varchar(255) | YES  | MUL | NULL    |       |
| usercode | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

两个字段都一样，才报错

mysql> insert into t_user2(id,username,usercode)values(15,'zhangsan','23');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user2(id,username,usercode)values(15,'zhangsan','25');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user2(id,username,usercode)values(15,'zhang','23');
Query OK, 1 row affected (0.00 sec)
//同时一样，才报错
mysql> insert into t_user2(id,username,usercode)values(15,'zhangsan','23');
ERROR 1062 (23000): Duplicate entry 'zhangsan-23' for key 'username'

主键约束

主键是某一行记录的唯一标识，相当于身份证号
主键不能为空，也不能重复

mysql> create table t_user(id int primary key,//也是列级约束
name varchar(255),email varchar(255));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_user(id,name,email)values(12,'lisi','23@qq.com');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+----+------+-----------+
| id | name | email     |
+----+------+-----------+
| 12 | lisi | 23@qq.com |
+----+------+-----------+
1 row in set (0.00 sec)

mysql> insert into t_user(id,name,email)values(12,'wangwui','23@qq.com');
ERROR 1062 (23000): Duplicate entry '12' for key 'PRIMARY'

mysql> insert into t_user(name,email)values('wangwui','23@qq.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

主键约束	primary key
主键字段	id
主键值	12

主健的分类

根据主键字段的字段数量来划分:
单一主键
复合主键(多个字段联合起来添加一个主键约束)（不建议使用）
根据主键性质来划分:
自然主键：主键值和业务脱钩（用单纯的序号作为主键，防止换了业务后不再适用）
业务主键：主键值和业务挂钩（身份证号作为主键）

一张表的主键约束只能有1个。

mysql提供主键自增，这样可以不用自己赋值了

create table t_user(
id int primary key auto_increment,//自增
email varchar(255)
)

mysql> insert into t_user(email)values('123@qq.com');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user(email)values('13@qq.com');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user(email)values('23@qq.com');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+------------+
| id | email      |
+----+------------+
|  1 | 123@qq.com |
|  2 | 13@qq.com  |
|  3 | 23@qq.com  |
+----+------------+

外键约束

外键约束	foreign key
外键字段	添加有外键约束的字段
外键值	外键字段中的每一个值

业务背景:
请设计数据库表,用来维护学生和班级的信息?
第一种方案:一张表存储所有数据

no (pk)	name	classno	classname
1	zs1	101	北京大兴区经济技术开发区亦庄二中高三1班
2	zs2	101	北京大兴区经济技术开发区亦庄二中高三1班
3	zs3	102	北京大兴区经济技术开发区亦庄二中高三2班
4	zs4	102	北京大兴区经济技术开发区亦庄二中高三2班
5	zs5	102	北京大兴区经济技术开发区亦庄二中高三2班

第二种方案：两张表存储数据
父表：
t_class 班级表

cno	cname
101	北京大兴区经济技术开发区亦庄二中高三1班
102	北京大兴区经济技术开发区亦庄二中高三2班

子表：
t_student

sno	sname	classno(外键约束)
1	zs1	101
2	zs2	101
3	zs3	102
4	zs4	102
5	zs5	102

t_student中的classno字段引用t_class表中的cno字段
此时t_student表叫做子表。t_class表叫做父表。
顺序要求:
删除数据的时候,先删除子表,再删除父表。
添加数据的时候,先添加父表,在添加子表。
创建表的时候,先创建父表,再创建子表。
删除表的时候,先删除子表,在删除父表。

//创建父表
create table t_class( cno int, cname varchar(255),primary key(cno));

//创建子表
mysql> create table t_student(sno int,sname varchar(255),
classno int,foreign key(classno) references t_class(cno));//利用foreign key来建立外键
//通过写references来定位父类
Query OK, 0 rows affected (0.02 sec)

在此基础上添加数据

mysql> insert into t_class values(101,'aaa');
mysql> insert into t_class values(102,'bbba');
mysql> insert into t_class values(103,'caa');
mysql> insert into t_student values(1,'ls',101);
mysql> insert into t_student values(2,'ls2',102)
mysql> insert into t_student values(3,'wa2',103);
mysql> insert into t_student values(4,'waza4',103);
//注意，这里不能给101 102 103以外的值，因为t_class中只有这三个数据
mysql> insert into t_student values(5,'aa4',105);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
(`mysql`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))

mysql> select * from t_class;
+-----+-------+
| cno | cname |
+-----+-------+
| 101 | aaa   |
| 102 | bbba  |
| 103 | caa   |
+-----+-------+
3 rows in set (0.02 sec)

mysql> select * from t_student;
+------+-------+---------+
| sno  | sname | classno |
+------+-------+---------+
|    1 | ls    |     101 |
|    2 | ls2   |     102 |
|    3 | wa2   |     103 |
|    4 | waza4 |     103 |
+------+-------+---------+

写好的这些代码可以一口气放在一个sql文件里面，然后通过source的方式直接用
Test.sql

drop table if exists t_student;
drop table if exists t_class;

create table t_class( cno int, cname varchar(255),primary key(cno));
create table t_student(sno int,sname varchar(255),
classno int,foreign key(classno) references t_class(cno));

insert into t_class values(101,'aaa');
insert into t_class values(102,'bbba');
insert into t_class values(103,'caa');
insert into t_student values(1,'ls',101);
insert into t_student values(2,'ls2',102);
insert into t_student values(3,'wa2',103);
insert into t_student values(4,'waza4',103);
select * from t_class;
select * from t_student;

mysql> source F:\编程学习\MySQL_Course\bjpowernode\Test.sql
Query OK, 0 rows affected (0.01 sec)

+-----+-------+
| cno | cname |
+-----+-------+
| 101 | aaa   |
| 102 | bbba  |
| 103 | caa   |
+-----+-------+
3 rows in set (0.00 sec)

+------+-------+---------+
| sno  | sname | classno |
+------+-------+---------+
|    1 | ls    |     101 |
|    2 | ls2   |     102 |
|    3 | wa2   |     103 |
|    4 | waza4 |     103 |
+------+-------+---------+
4 rows in set (0.00 sec)

外键可以为null

mysql> insert t_student(sno,sname)values(2,'101');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
+------+-------+---------+
| sno  | sname | classno |
+------+-------+---------+
|    1 | ls    |     101 |
|    2 | ls2   |     102 |
|    3 | wa2   |     103 |
|    4 | waza4 |     103 |
|    2 | 101   |    NULL |
+------+-------+---------+