关系型数据库中联合主键和唯一索引的应用

一、前言

       在项目开发中往往会遇到两个实体对象之间存在多对多关系的情况,此时我们会维护两个实体对象表,一个关系表,用来存放两者之间的关系。比较典型的案例是学生表、课程表、学生课程关系表。在这种关系表中,我们可以确定的是,学生和课程关系虽然是多对多,但一个确定的学生id和一个确定的课程id在关系表中只能存在一个。如下图所示:,业务逻辑就是一个学生只能有当前课程的一个分数。

t_student                                                t_course                                          t_student_course_rel   

                                 

         因此可以得知,通过student_id和course_id可以唯一确认一个t_student_course_rel数据,相同的student_id、course_id最多只能存在一条数据。按照正常的业务逻辑来说,不会出现意外的情况,但是一旦出现了,对于以后的查询操作将是一个潜在的隐患,如 select * from t_student_course_rel   where student_id=1 and course_id = 2 ,此时我们希望只查出来一条数据,但如果出现了意外情况,则可能对应出来多条数据,这样便会报错。 

        导致意外的情况:后端高并发的操作没有做出处理、插入数据时没有做验证等等。

        解决方案:1. 对高并发操作加锁, 并且在插入数据时做验证。2. 可在数据库层面做出限制,如联合主键和唯一索引均可

        接下来,以这几个表为例,分析使用两者之间的区别。

二、联合主键和唯一索引的应用

        上述提到,可以对 student_id和 course_id做联合主键和唯一索引,均可以实现在数据库层面对关系表中异常数据做出限制,我们如何选择呢?

1.使用联合主键限制

        如果是在最初建关系表t_student_course_rel 的时候,就已经考虑到这个需求的话,可以考虑将student_id和course_id直接当做联合主键去使用。建表语句如下图所示:

CREATE TABLE `t_student_course_rel ` (
`id`  bigint(20) NOT NULL ,
`student_id`  bigint(20) NOT NULL ,
`course_id`  bigint(20) NOT NULL ,
`marks`  int(11) NOT NULL DEFAULT 0 ,
PRIMARY KEY (`student_id`, `course_id`)
)

        已经建立联合主键的关系表,在面对两个相同student_id和course_id的数据插入时,会抛出主键重复的异常,如下图所示

表中已经有了student_id=1和course_id=1的数据,如果再插入便会报错。

                                                    

                                             

2.使用唯一索引限制

        如果当前的表已经运行了很久,不适合再去修改表结构时,或者是表中的主键已经确定为其他字段时,便不再适合使用联合主键,则可以考虑对表增加非聚集索引之一的唯一索引对关系表做出限制。即将student_id和course_id建立表的唯一索引。其中sql如下:

CREATE TABLE `t_student_course_rel ` (
`id`  bigint(20) NOT NULL ,
`student_id`  bigint(20) NOT NULL ,
`course_id`  bigint(20) NOT NULL ,
`marks`  int(11) NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`),
UNIQUE INDEX `in_rel` (`student_id`, `course_id`) USING BTREE 
)

        (如果表已经建成,则直接用sql加唯一索引即可)

         同样,已经建立联合唯一索引的关系表,在面对两个相同student_id和course_id的数据插入时,会抛出主键重复的异常,即可解决。

                             

三、结论

         对关系表中的单个关系的两个外键id做出联合主键以及联合唯一索引,可以有效限制关系表中错误数据以及异常数据的产生(不管是中期的数据插入,还是后期的数据迁移,都能有效地做出限制),而两者的选择就看需求了。

1.联合主键:在创建表的初期就考虑到这个问题,可以对两个外键id做联合主键,相对于索引来说会省去一些空间。

2.唯一索引:可以在后期加入索引,对两个外键id做唯一索引,不会修改表结构,但相对来说会增加一些空间。

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页