关系模型——主键、外键、索引

主键

主键：唯一识别字段

要求：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。

基本选取原则：选择与业务无关的字段，命名id

id字段常用类型：

自增整数BIGINT类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；

全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

联合主键：关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键 ；对于联合主键，允许一列有重复，只要不是所有主键链均重复即可

test

id_num	id_type	other columns...
1	A	......
2	A	......
2	B	......

如果我们把上述表的id_num和id_type这两列作为联合主键，那么上面的3条记录都是允许的，因为没有两列主键组合起来是相同的。

没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。

 外键

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

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

 

外键：在students表中，通过class_id这一字段，将数据与另一张表关联起来，此时这个class_id，称之为外键

注！外键并不是通过列名实现的，而是通过定义外键约束实现的

其中，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）

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

删除外键约束：

注！删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

多对多：

通过一个表的外键关联到另一个表，我们可以定义出一对多关系。多对多”关系。就是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系

多对多实际上是俩个一对多关系实现的，通过中间表，关联俩个一对多的关系，行成了多对多的关系。

teachers

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

 

class

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

 

通过中间表teacher_class，可知班级表class与老师表teachers之间的关系

• id=1的张老师对应id=1,2的一班和二班
• id=2的王老师对应id=1,2的一班和二班
• id=3的李老师对应id=1的一班
• id=4的赵老师对应id=2的二班

同理，class到teachers的关系：

• id=1的一班对应id=1,2,3的张老师、王老师和李老师
• id=2的二班对应id=1,2,4的张老师、王老师和赵老师

一对一：

一个表的记录对应到另一张表的唯一记录

索引

关系数据库中，对某一列或多个列的值进行预排序的数据结构，通过使用索引，可以让数据库系统不用扫描整个表，而是直接定位到符合条件的记录，加快查询速度。

创建索引：ADD INDEX

索引的效率取决于索引列的值是否相同，即该列的值越不相同，那么索引效率越高。

对于主键，关系型数据库会自动创建主键索引，因为主键是唯一的，所以使用主键效率最高

ALTER TABLE students
ADD INDEX idx_score(score);
使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。
索引名称是任意的，索引如果有多列，可以在括号里依次写上
ALTER TABLE students
ADD INDEX idx_score(name,score);

索引优缺点：

优点：提高查询效率

缺点：在增、删、改时，需要同时修改索引，因此，索引越多，增、删、改速度越慢

唯一索引：

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。

但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，我们假设students表的name不能重复：

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

通过UNIQUE关键字我们就添加了一个唯一索引。
也可以只对某一列添加一个唯一约束而不创建唯一索引。

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);