一、主键约束PK(Primary Key)
1.1 需求:中国公民身份信息
id | name | sex | age |
身份证号 | 张三 | 男 | 27 |
身份证号 | 李四 | 男 | 28 |
1.2 主键设计到的3个术语:主键约束、主键字段、主键值
表中某个字段添加主键约束之后,该字段被称为主键字段,主键字段中出现的每一个数据都被称为主键值
1.3 主键作用
1)添加主键primary key的字段即不能重复也不能为空,效果与“not null nuique”相同,但本质是不同的,添加主键约束后,主键不仅会有“not null unique”作用,而且主键字段还会自动添加“索引——index”
2)一张表应该有主键,若没有,表示这张表时无效的,“主键值”是当前行数据的唯一标识
1.4 主键根据个数分类:单一主键,复合主键
1)单一主键:是给一个字段添加主键约束
列级主键约束
drop table if exists t_user;
create table t_user(
id int(10) primary key,
name varchar(32)
);
表级主键约束
drop table if exists t_user;
create table t_user(
id int(10),
name varchar(32),
constraint t_user_id_pk primary key(id)
);
2)复合主键:是给多个字段联合添加一个主键约束:
drop table if exists t_user;
create table t_user(
id int(10),
name varchar(32),
email varchar(128),
constraint t_user_id_name_pk primary key(id,name)
);
1.5 表中主键个数(只能有一个)
无论是单一主键还是复合主键,一张表中主键约束只能有一个
1.6 主键根据性质分类:自然主键、业务主键
1)自然主键:主键值若是一个自然数,并且这个自然数与业务没有任何关系,这种主键称为自然主键;
2)业务主键:主键值若和当前表中的业务紧密相关,那么这种主键值被业务主键,如果业务发生改变时,业务主键往往会受到影响,所以业务主键使用较少,大多情况使用自然主键。
1.7 MySQL中自动生成主键值(MySQL特有)
1)定义:MySQL数据库管理系统中提供了一个自增数字auto_increment,专门用来自动生成主键值。自增数字默认从1开始,以1递增:1、2、3....
2)需求:创建t_user表,id name,id为自增主键
drop table if exists t_user;
create table t_user(
id int(10) primary key auto_increment,
name varchar(32)
);
二、外键约束FK(foreign key)
2.1 外键涉及到的术语:外键约束、外键字段、外键值
给某个字段添加外键约束之后,该字段称为外键字段,外键字段中的数据称为外键值。
2.2 外键根据个数分为:单一外键、复合外键
1)单一外键:给一个字段添加外键约束
2)复合外键:给多个字段添加一个外键
2.3 外键在同一张表中可以有多个外键存在
需求:设计数据表用来存储学生和班级信息
需求分析:
a)学生表t_student包括:sno,sname,classno,cname
b)学生信息和班级信息之间的关系:一个班级对应多个学生,典型的一对多关系;
1)第一种解决方案:将学生信息和班级信息存储到同一张表中
学生信息表:t_student
sno | sname | classno | cname |
1 | Jack | 100 | 高三1班 |
2 | Lucy | 100 | 高三1班 |
3 | Lily | 200 | 高三2班 |
缺点:数据
2)第二种解决方案:将学生信息和班级信息分开两张表存储:学生表+班级表
思路分析:
a)创建t_student表和t_class表
b)t_student表要与t_class表有关系,需要在t_student表中添加一个classno字段作为外键
c)结论:为了保证t_student表中的classno字段中的数据必须来自t_class表中cno字段中数据,有必要给t_student表中classno字段添加外键约束;classno称为外键字段
注意:
①外键字段可以为NULL,外键为空的数据也叫孤儿数据;
②被引用字段必须具有unique约束
③有了外键引用之后,表分为父表和子表,父表:t_class;子表:t_student;创建表时先创建父表,在创建子表;插入数据时,先插入父表数据,再插入子表数据;
ROP TABLE IF EXISTS t_student;
CREATE TABLE t_student(
sno INT(4) PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(32),
classno INT(4),
CONSTRAINT t_student_classno_fk FOREIGN KEY(classno) REFERENCES t_class(cno)
);
DROP TABLE IF EXISTS t_class;
CREATE TABLE t_class(
cno INT(4) PRIMARY KEY,
cname VARCHAR(32)
);
2.4 找出每个学生的班级名称
SELECT s.sname,c.cname FROM t_student s JOIN t_class c ON s.`classno` = c.`cno`;
重点:典型的一对多设计,在多的一方加外键
2.5 级联删除
用法:在添加级联更新与级联删除的时候,需要在外键约束后面添关键字
注意:级联更新与级联删除操作谨慎使用,因为级联操作会将数据改变或者删除
1)级联删除:on delete cascade
定义:在删除父表数据的时候,级联删除子表中数据;
a)删除外键约束
ALTER TABLE t_student DROP FOREIGN KEY t_student_classno_fk;
b)添加外键约束及级联删除功能
语法:alter table 表名 add constraint 外键名称 foreign key(外键字段) references 引用表名(引用表中字段名称) on delete cascade;
c)级联删除需求
①删除t_student中的外键
ALTER TABLE t_student DROP FOREIGN KEY t_student_classno_fk;
②增加t_student_classno_fk外键,并加入级联删除 on delete cascade;
ALTER TABLE t_student ADD CONSTRAINT t_student_classno_fk FOREIGN KEY(classno) REFERENCES t_class(cno) ON DELETE CASCADE;
2.6 级联更新
定义:在更新父表中数据的时候,级联更新子表中数据;
a)删除外键约束
语法:alter table 表名 drop foreign key 外键字段;
ALTER TABLE t_student DROP FOREIGN KEY t_student_classno_fk;
b)更新外键约束和添加级联更新功能
语法:alter table 表名 add constraint 外键名称 foreign key(外键字段) references 引用表名(引用表中字段名称) on update cascade
c)级联更新需求
①删除t_student中的外键
ALTER TABLE t_student DROP FOREIGN KEY t_student_classno_fk;
②增加t_student_classno_fk外键,并加入级联更新 on update cascade;
ALTER TABLE t_student ADD CONSTRAINT t_student_classno_fk FOREIGN KEY(classno) REFERENCES t_class(cno) ON UPDATE CASCADE;