0%

13.2.5 数据库约束 4.foreign key约束

发表于 更新于 分类于 ， ，

13.2.5 数据库约束 4.FOREIGN KEY约束

外键约束主要用于保证一个或两个数据表之间的参照完整性

从表

增加外键列的表被称为从表，只要为外键列增加唯一约束就可表示一对一的关联关系了。
外键是构建于一个表的两个字段或者两个表的两个字段之间的参照关系。外键确保了相关的两个字段的参照关系：子（从）表外键列的值必须在主表被参照列的值范围之内，或者为空（也可以通过非空约束来约束外键列不允许为空）。

当主表的记录被从表记录参照时，主表记录不允许被删除，必须先把从表里参照该记录的所有记录全部删除后，才可以删除主表的该记录。还有一种方式，删除主表记录时级联删除从表中所有参照该记录的从表记录。
从表外键参照的只能是主表主键列或者唯一键列，这样才可保证从表记录可以准确定位到被参照的主表记录。同一个表内可以拥有多个外键。
建立外键约束时，MySQL也会为该列建立索引。

如何确定外键设置的位置

外键约束通常用于定义两个实体之间的一对多、一对一的关联关系。

一对多关联关系

对于一对多的关联关系，通常在多的一端增加外键列，
例如老师—学生（假设一个老师对应多个学生，但每个学生只有一个老师，这是典型的一对多的关联关系）。为了建立他们之间的关联关系，可以在学生表中增加一个外键列，该列中保存此条学生记录对应老师的主键。

一对一关联关系

对于一对一的关联关系，则可选择任意一方来增加外键列，

多对多联系

对于多对多的关联关系，则需要额外增加一个连接表来记录它们的关联关系。

外键约束写在哪里

建立外键约束同样可以采用列级约束语法和表级约束语法。如果仅对单独的数据列建立外键约束，则使用列级约束语法即可；如果需要对多列组合创建外键约束，或者需要为外键约束指定名字，则必须使用表级约束语法。

列级约束语法建立外键约束

采用列级约束语法建立外键约束直接使用references关键字，references指定该列参照哪个主表，以及参照主表的哪一列
语法格式如下:

1
references 主表(主表的列)

如下SQL语句所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
create table teacher_table
(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);
creater table student_table
(
    student_id int auto_increment primary key,
    student_name varchar(255),
    # 这是一个外键,参照teacher_table表的teacher_id属性
    java_teacher int references teacher_table(teacher_id)
);

MySQL中外键约束正确语法

值得指出的是，虽然MySQL支持使用列级约束语法来建立外键约束，但这种列级约束语法建立的外键约束不会生效，MySQL提供这种列级约束语法仅仅是为了和标准SQL保持良好的兼容性。因此，如果要使MySQL中的外键约束生效，则应使用表级约束语法,也就是要写在表定义的后面,不要写在列定于中,如下所示:。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table teacher_table1
(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);
create table student_table
(
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    #指定java_teacher参照到teacher_table1的teacher_id列
    foreign key(java_teacher)
        referenses teacher_table1(teacher_id)
);

如果使用表级约束语法，则需要使用foreign key来指定本表的外键列，并使用references来指定参照哪个主表，以及参照到主表的哪个数据列。
格式如下所示:

1
foreign key(本表中的列名) referenses 主表名(主表的列明)

使用表级约束语法可以为外键约束指定约束名，如果创建外键约束时没有指定约束名，则MySQL会为该外键约束命名为table_name_ibfk_n，其中table_name是从表的表名，而n是从1开始的整数。

指定外键约束的名字

如果需要显式指定外键约束的名字，则可使用constraint来指定名字。如下SQL语句所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table teacher_table2
(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);
create table student_table2
(
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    constraint student_teacher_fk foreign key(java_teacher)
        references teacher_table2(teacher_id)
);

组合外键

如果需要建立多列组合的外键约束，则必须使用表级约束语法，如下SQL语句所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table teacher_table3
(
    teacher_name varchar(255),
    teacher_pass varchar(255),
    # 以两个键作为主键
    primary key(teacher_id,teacher_pass)
);
create table student_table3
(
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher_name varchar(255),
    java_teacher_pass varchar(255),
    # 指定两列的联合外键
    foreign key(java_teacher_name,java_teacher_pass)
        referenses teacher_table3(teacher_name,teacher_pass)
);

删除外键约束

删除外键约束的语法很简单，在alter table后增加“drop foreign key 约束名”子句即可。如下代码所示：

1
2
alter table student_table3
drop foreign key student_table3_ibfk_i;

增加外键约束

增加外键约束通常使用add foreign key命令。如下SQL语句所示：

1
2
3
alter table student_table3
add foreign key(java_teacher_name,java_teacher_pass)
    references teacher_table3(teacher_name,teacher_pass);

自关联

值得指出的是，外键约束不仅可以参照其他表，而且可以参照自身，这种参照自身的情况通常被称为自关联。例如，使用一个表保存某个公司的所有员工记录，员工之间有部门经理和普通员工之分，部门经理和普通员工之间存在一对多的关联关系，但他们都是保存在同一个数据表里的记录，这就是典型的自关联。下面的SQL语句用于建立自关联的外键约束。

1
2
3
4
5
6
7
8
9
create table foreign_test
(
    foreign_id int auto_increment primary key,
    foreign_name varchar(255),
    #refer_id参照本表的foreign_id列
    refer_id int,
    foregin key(refer_id)
        references foreign_test(foreign_id)
);

删除主表记录时从表记录也删除

如果想定义当删除主表记录时，从表记录也会随之删除，则需要在建立外键约束后添加on delete cascade或添加on delete set null，第一种是删除主表记录时，把参照该主表记录的从表记录全部级联删除；第二种是指定当删除主表记录时，把参照该主表记录的从表记录的外键设为null。如下SQL语句所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table teacher_table4
(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);
create table student_table4
(
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    foreign key(java_teacher)
        references teacher_table4(teacher_id)
        on delete cascade #也可以用on delete set null
);

原文链接: 13.2.5 数据库约束 4.foreign key约束