SQL主键、外键、索引

主键：唯一区分出不同的记录的字段。

选取主键原则：不使用任何业务相关的字段作为主键。

常见可作为id字段的类型：

1. 自增整数类型
2. 全局唯一GUID类型（GUID算法通过网卡MAC地址、时间戳和随机数生成的字符串）

联合主键：两个或更多的字段都设置为主键。（允许一列有重复）

 

外键：实现一对多、多对多和一对一的关系。

可以通过数据库来约束，也可以不设置约束，仅靠应用程序的逻辑来保证。 

一对多： 

学生表：students

id	name	other columns...
1	小明	...
2	小红	...

班级表：classes

id	name	other columns...
1	一班	...
2	二班	...

id	class_id	name	other columns...
1	1	小明	...
2	1	小红	...
3	2	小白	...

定义外键约束：

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

fk_class_id  :  外键约束名称

FOREIGN KEY (class_id)  ：指定class_id作为外键

REFERNCES classes (id) ：指定将外键关联到classes表的主键 

外键约束的作用：保证关系型数据库无法插入无效的数据。 

删除一个外键约束： 

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列。删除列：DROP COLUMN ...实现。

 

多对多：通过两个一对多关系实现，即通过一个中间表，关联两个一对多关系。

老师表：teachers

id	name
1	张老师
2	王老师
3	李老师
4	赵老师

 班级表：classes

id	name
1	一班
2	二班

 中间表：teacher_class (关联两个一对多关系)

id	teacher_id	class_id
1	1	1
2	1	2
3	2	1
4	2	2
5	3	1
6	4	2

 一对一：一个表的记录对应到另一个表的唯一记录。

 

索引：关系型数据库中对某一列或多列值进行预排序的数据结构。

学生表：students 

id	class_id	name	gender	score
1	1	小明	M	90
2	1	小红	F	95
3	1	小军	M	88

如果经常操作score列进行查询，就可以对score列创建索引：

ALTER TABLE students
ADD INDEX idx_score (score);

创建一个名称为idx_score,使用列score的索引。

优点：提高查询效率。

缺点：插入、更新、删除记录时，需要同时修改索引，因此插入、更新、删除记录速度变慢。

对于主键，关系型数据库会自动对其创建主键创建索引。