美文网首页
外键与表设计

外键与表设计

作者: 二歪求知iSk2y | 来源:发表于2018-11-05 17:38 被阅读0次

    外键设置

    在设计表的时候,如果某个表中的同一内容字符重复使用,而且只有那些选项(或者后续增加),可以新建一个表,表内存储选项内容,和原表组成外键联系。

    场景:

    假设我们要描述所有公司的员工,需要描述的属性有这些 : 工号 姓名 部门

    公司有3个部门,但是有1个亿的员工,那意味着部门这个字段需要重复存储,部门名字越长,越浪费

    下面以departments和staff_info表为例(为staff_info添加指向departments的外键)

    创建外键的条件

    1. 被关联列(references)必须非空且唯一

    mysql> create table departments (dep_id int(4),dep_name varchar(11));
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> desc departments;
    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | dep_id   | int(4)      | YES  |     | NULL    |       |
    | dep_name | varchar(11) | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    rows in set (0.00 sec)
    
    # 创建外键不成功
    mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
    ERROR 1215 (HY000): Cannot add foreign key 
    
    # 设置dep_id非空,仍然不能成功创建外键
    mysql> alter table departments modify dep_id int(4) not null;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> desc departments;
    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | dep_id   | int(4)      | NO   |     | NULL    |       |
    | dep_name | varchar(11) | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    rows in set (0.00 sec)
    
    mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
    ERROR 1215 (HY000): Cannot add foreign key constraint
    
    # 当设置字段为unique唯一字段时,设置该字段为外键成功
    mysql> alter table departments modify dep_id int(4) unique;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> desc departments;                                                               +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | dep_id   | int(4)      | YES  | UNI | NULL    |       |
    | dep_name | varchar(11) | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    rows in set (0.01 sec)
    
    mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));
    Query OK, 0 rows affected (0.02 sec)
    
    创造外键的条件
    

    两个表必须都是innodb存储引擎

    添加外键的方法

    一般有两种方法,在创建表的时候添加,或者后期再添加

    创建时添加

    mysql> create table score(
        -> sid int not null auto_increment primary key,
        -> student_id int,
        -> corse_id int,
        -> number int not null,
        -> constraint fk_sid foreign key (student_id) references student(sid),
        -> constraint fk_corse_id foreign key (corse_id) references course(cid));
     
    -----------------------------------------------------------------------------------
    [CONSTRAINT symbol] FOREIGN KEY [id] (从表的字段1)
    
    REFERENCES tbl_name (主表的字段2)
    
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION} 
    

    CONSTRAINT symbol:可以给这个外键约束起一个名字,有了名字,以后找到它就很方便了。如果不加此参数的话,系统会自动分配一个名字。

    FOREIGN KEY:将从表中的字段1作为外键的字段。

    REFERENCES:映射到主表的字段2。

    ON DELETE后面的四个参数:代表的是当删除主表的记录时,所做的约定。

    • RESTRICT(限制):如果你想删除的那个主表,它的下面有对应从表的记录,此主表将无法删除。
    • CASCADE(级联):如果主表的记录删掉,则从表中相关联的记录都将被删掉。
    • SET NULL:将外键设置为空。
    • NO ACTION:什么都不做。

    创建后添加

    ALTER TABLE employee ADD FOREIGN KEY(dept_id) REFERENCES department(id); 
    

    ALTER TABLE employee:在从表employee中进行操作;

    ADD FOREIGN KEY(dept_id):将从表的字段dept_id添加为外键;

    REFERENCES department(id):映射到主表department当中为id的字段。

    删除外键

    删除外键时如果你不知道外键名字 就先获取外键名字

    获取外键名

    show create table emp \G;
    

    删除外键

    alter table emp drop foreign key 外键名; 
    

    关系的实现

    在实体关系模型中,我们知道有三种关系:一对一、一对多、多对多。这只是概念上的关系,但是在真实的关系数据库中,我们只有外键,并没有这三种关系,那么我们就来说一说在关系数据库管理系统中,怎么实现这三种关系。

    一对多

    这里先讲解一对多,因为这个关系最简单。一对多和多对一是一回事,所以就不再提多对一这个词。一对多的概念是一个对象A会对应多个对象B,而从B的角度看,一个对象B只会对于一个对象A。比如说班级和学生就是一对多关系。一个班级对应多个学生,一个学生只会对于一个班级。

    ​ 一对多的关系之所以说简单,是因为RDBMS的【外键】其实就是表示一对多关系。对于一对多关系,我们只需要在“多”的这个表中建立“一”的外键关联即可,而“一”这边的表不需要做任何修改。比如前面说到的班级学生关系。班级表不变,学生表增加班级Id作为外键。

    多对多

    多对多的关系在数据库设计时比一对一要常见,所以这里先说说多对多。多对多是一个对象A对应多个对象B,从B角度看,一个对象B也会对应多个对象A。比如说学生和课程的关系就是多对多关系。一个学生会学习多门课程,一门课程会有多个学生来选修。

    ​ 在RDBMS中,必须使用中间表来表示多对多的关系。中间表我们可以分成两种,一种是纯粹表示关系的中间表,一种是表示中间实体的中间表。

    ​ 纯粹表示关系的中间表很简单,只需要两列:AID和BID,AID以外键关联到A表的主键,BID以外键关联到B表的主键,然后这两个列组成联合主键。这个中间表纯粹是表示多对多关系而存在,在业务上不会有对应的实体与之对应。比如前面提到的学生和课程的关系,如果我们只需要知道哪些学生上哪些课,哪些课有哪些学生选,不需要有更多的信息的情况下,我们就可以建立“学生课程”中间表,里面只有学生ID和课程ID两个字段。

    ​ 中间实体是在纯粹的中间关系表的基础上,加上了更多的属性,从而形成了一个新的实体。比如前面提到的学生和课程的关系,如果我们需要记录学生选课的时间、学生选择这门课程后的考试成绩,那么我们就像建立一个“选课”实体,该实体具有如下属性:

    • 选课ID,主键
    • 学生ID,与学生表做外键关联
    • 课程ID,与课程表做外键关联
    • 选课时间,DateTime类型
    • 考试成绩,记录选修该课程后考试的最终成绩

    ​ 这就是一个中间实体,已经完全脱离了普通的多对多关系中间表,而变成一个实体的形式的存在,所以按照前面博客中讲到的主键设计的原则,我们可以单独建立一个选课ID的列作为数据库的主键,该主键本身并没有业务含义。

    一对一

    一对一概念上是说一个对象A最多对应一个对象B,从B角度看,也是一个对象B最多对应一个对象A。比如说班主任(教师)和班级的关系,一个班主任最多管理一个班级,一个班级也最多只有一个班主任。

    ​ 一对一的关系在数据库设计中,是使用的最少的关系,因为一般来说,如果两个实体是一对多关系,那么我们也可以把这两个实体合并成一个实体。但是在设计中,我们仍然会遇到两个完全不同的实体,之间存在一对一关系。

    ​ 一对一的RDBMS实现是在其中的一个表上建立外键指向另一个表,同时在该外键列上建立唯一约束。比如前面说到的班主任和班级关系,我们可以在班级表建立班主任字段,然后再在该字段建立唯一约束。因为每个班都会有班主任,所以班主任字段是不允许为空的。一个教师可以当某个班的班主任,也可以不当任和班的班主任,同时也不可能在班级表的班主任字段上出现两次,所以最多就当一个班的班主任,所以该设计满足需求。

    ​ 那么我们可不可以反过来,在教师表中建立所管理的班级Id字段,指向班级表,并建立唯一约束呢?除了不满足“每个班必然有一个班主任”这个业务约束外,其他都没有问题。所以如果对于一对一的情况,如果那边必须要求持有另一边,则就在哪边增加外键字段;如果没有要求必须持有一个另一类实体的话,就哪边添加外键列都行。

    /**学生表*/
    CREATE TABLE student (
    stu_id INT AUTO_INCREMENT,
    NAME VARCHAR(30),
    age INT ,
    class VARCHAR(50),
    address VARCHAR(100),
    PRIMARY KEY(stu_id)
    )
     
    /*学生课程表*/
    CREATE TABLE Course(
    cour_id INT AUTO_INCREMENT,
    NAME VARCHAR(50),
    CODE VARCHAR(30),
    PRIMARY KEY(cour_id)
    )
     
    /**学生课程关联表*/
    CREATE TABLE Stu_Cour(
    sc_id INT AUTO_INCREMENT,
    stu_id INT ,
    cour_id INT,
    PRIMARY KEY(sc_id)
    )
    
    /*添加外键约束*/
    ALTER TABLE Stu_Cour ADD CONSTRAINT stu_FK1 FOREIGN KEY(stu_id) REFERENCES student(stu_id)
    ALTER TABLE Stu_Cour ADD CONSTRAINT cour_FK2 FOREIGN KEY(cour_id) REFERENCES Course(cour_id)
    

    参考资料

    数据库表设计(一对多,多对多) 【好】

    数据库模型设计——关系的实现,主键的设计

    mysql表的完整性约束

    相关文章

      网友评论

          本文标题:外键与表设计

          本文链接:https://www.haomeiwen.com/subject/xwosxqtx.html