MariaDB数据库的外键约束实例代码介绍详解
文章主要给大家介绍了关于MariaDB数据库的外键约束的相关资料,文中通过示例代码介绍的非常详细,需要的朋友可以参考借鉴,下面随着小编来一起学习学习吧
外键
外键的用途是确保数据的完整性。它通常包括以下几种:
1 实体完整性,确保每个实体是唯一的(通过主键来实施)
2 域完整性,确保属性值只从一套特定可选的集合里选择
3 关联完整性,确保每个外键或是NULL(如果允许的话)或含有与相关主键值相配的值
1.什么是外键约束
与主键约束不同,创建外键约束不会自动创建对应的索引。 但是由于以下原因,对外键手动创建索引通常是有用的:
当在查询中组合相关表中的数据时,经常在联接条件中使用外键列,方法是将一个表的外键约束中的一列或多列与另一个表中的主键列或唯一键列匹配。 索引使 数据库引擎 可以在外键表中快速查找相关数据。 但是,创建此索引并不是必需的。 即使没有对两个相关表定义主键或外键约束,也可以对来自这两个表中的数据进行组合,但两个表间的外键关系说明已用其键作为条件对其进行了优化,以便组合到查询中。
对主键约束的更改可由相关表中的外键约束检查。
外键约束(foreign key)就是表与表之间的某种约定的关系,由于这种关系的存在,我们能够让表与表之间的数据,更加的完整,关连性更强。
关于数据表的完整性和关连性,可以举个例子
有二张表,一张是用户表,一张是订单表:
1.如果我删除了用户表里的用户,那么订单表里面跟这个用户有关的数据,就成了无头数据了,不完整了。
2.如果我在订单表里面,随便插入了一条数据,这个订单在用户表里面,没有与之对应的用户。这样数据也不完整了。
如果有外键的话,就方便多了,可以不让用户删除数据,或者删除用户的话,通过外键同样删除订单表里面的数据,这样也能让数据完整。
通过外键约束,每次插入或更新数据表时,都会检查数据的完整性。
2.创建外键约束
2.1 方法一:通过create table创建外键
语法:
create
table
数据表名称(
...,
[
CONSTRAINT
[约束名称]]
FOREIGN
KEY
[外键字段]
REFERENCES
[外键表名](外键字段,外键字段2…..)
[
ON
DELETE
CASCADE
]
[
ON
UPDATE
CASCADE
]
)
参数的解释:
RESTRICT: 拒绝对父表的删除或更新操作。
CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用
注意:on update cascade是级联更新的意思,on delete cascade是级联删除的意思,意思就是说当你更新或删除主键表,那外键表也会跟随一起更新或删除。
精简化后的语法:
foreign
key
当前表的字段
references
外部表名 (关联的字段) type=innodb
2.1.1 插入测试数据
例子:我们创建一个数据库,包含用户信息表和订单表
MariaDB [book]>
create
database
market; # 创建market数据库
Query OK, 1 row affected (0.00 sec)
MariaDB [book]> use market; # 使用market数据库
Database
changed
MariaDB [market]>
create
table
userprofile(id
int
(11)
not
null
auto_increment,
name
varchar
(50)
not
null
default
''
, sex
int
(1)
not
null
default
'0'
,
primary
key
(id))ENGINE=innodb; # 创建userprofile数据表,指定使用innodb引擎
Query OK, 0
rows
affected (0.07 sec)
MariaDB [market]>
create
table
user_order(o_id
int
(11) auto_increment, u_id
int
(11)
default
'0'
, username
varchar
(50), money
int
(11),
primary
key
(o_id),
index
(u_id),
foreign
key
order_f_key(u_id)
references
userprofile(id)
on
delete
cascade
on
update
cascade
); # 创建user_order数据表,同时为user_order表的u_id字段做外键约束,绑定userprofile表的id字段
Query OK, 0
rows
affected (0.04 sec)
MariaDB [market]>
insert
into
userprofile(
name
,sex)
values
(
'HA'
,1),(
'LB'
,2),(
'HPC'
,1); # 向userprofile数据表插入三条记录
Query OK, 3
rows
affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [market]>
select
*
from
userprofile; # 查询userprofile数据表的所有记录
+
----+------+-----+
| id |
name
| sex |
+
----+------+-----+
| 1 | HA | 1 |
| 2 | LB | 2 |
| 3 | HPC | 1 |
+
----+------+-----+
3
rows
in
set
(0.00 sec)
MariaDB [market]>
insert
into
user_order(u_id,username,money)
values
(1,
'HA'
,234),(2,
'LB'
,146),(3,
'HPC'
,256); # 向user_order数据表插入三条记录
Query OK, 3
rows
affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [market]>
select
*
from
user_order; # 查询user_order数据表的所有记录
+
------+------+----------+-------+
| o_id | u_id | username | money |
+
------+------+----------+-------+
| 1 | 1 | HA | 234 |
| 2 | 2 | LB | 146 |
| 3 | 3 | HPC | 256 |
+
------+------+----------+-------+
3
rows
in
set
(0.00 sec)
MariaDB [market]>
select
id,
name
,sex,money,o_id
from
userprofile,user_order
where
id=u_id; # 联表查询
+
----+------+-----+-------+------+
| id |
name
| sex | money | o_id |
+
----+------+-----+-------+------+
| 1 | HA | 1 | 234 | 1 |
| 2 | LB | 2 | 146 | 2 |
| 3 | HPC | 1 | 256 | 3 |
+
----+------+-----+-------+------+
3
rows
in
set
(0.03 sec)
2.1.2 测试级联删除
MariaDB [market]>
delete
from
userprofile
where
id=1; # 删除
user
表中id为1的数据
Query OK, 1 row affected (0.01 sec)
MariaDB [market]>
select
id,
name
,sex,money,o_id
from
userprofile,user_order
where
id=u_id;
+
----+------+-----+-------+------+
| id |
name
| sex | money | o_id |
+
----+------+-----+-------+------+
| 2 | LB | 2 | 146 | 2 |
| 3 | HPC | 1 | 256 | 3 |
+
----+------+-----+-------+------+
2
rows
in
set
(0.00 sec)
MariaDB [market]>
select
*
from
user_order; # 查看
order
表的数据
+
------+------+----------+-------+
| o_id | u_id | username | money |
+
------+------+----------+-------+
| 2 | 2 | LB | 146 |
| 3 | 3 | HPC | 256 |
+
------+------+----------+-------+
3
rows
in
set
(0.00 sec)
2.1.3 测试级联更新
更新数据之前的状态
MariaDB [market]>
select
*
from
userprofile; # 查看userprofile表的数据
+
----+------+-----+
| id |
name
| sex |
+
----+------+-----+
| 2 | LB | 2 |
| 3 | HPC | 1 |
+
----+------+-----+
3
rows
in
set
(0.00 sec)
MariaDB [market]>
select
*
from
user_order; # 查看
order
表的数据
+
------+------+----------+-------+
| o_id | u_id | username | money |
+
------+------+----------+-------+
| 2 | 2 | LB | 146 |
| 3 | 3 | HPC | 256 |
+
------+------+----------+-------+
3
rows
in
set
(0.00 sec)
更新数据?
MariaDB [market]>
update
userprofile
set
id=6
where
id=2; # 把userprofile数据表中id为2的用户改为id为6
Query OK, 1 row affected (0.02 sec)
Rows
matched: 1 Changed: 1 Warnings: 0
更新数据后的状态?
MariaDB [market]>
select
id,
name
,sex,money,o_id
from
userprofile,user_order
where
id=u_id; # 联表查询,可以看出表中已经没有id为2的用户了
+
----+------+-----+-------+------+
| id |
name
| sex | money | o_id |
+
----+------+-----+-------+------+
| 6 | LB | 2 | 146 | 2 |
| 3 | HPC | 1 | 256 | 3 |
+
----+------+-----+-------+------+
2
rows
in
set
(0.00 sec)
MariaDB [market]>
select
*
from
userprofile; # 查看userprofile表的数据,id只剩下3和6
+
----+------+-----+
| id |
name
| sex |
+
----+------+-----+
| 3 | HPC | 1 |
| 6 | LB | 2 |
+
----+------+-----+
2
rows
in
set
(0.00 sec)
MariaDB [market]>
select
*
from
user_order; # 查看user_order表的数据,u_id也改为6
+
------+------+----------+-------+
| o_id | u_id | username | money |
+
------+------+----------+-------+
| 2 | 6 | LB | 146 |
| 3 | 3 | HPC | 256 |
+
------+------+----------+-------+
2
rows
in
set
(0.00 sec)
2.1.4 测试数据完整性
MariaDB [market]>
insert
into
user_order(u_id,username,money)
values
(5,
"XJ"
,345); # 单独向user_order数据表中插入数据,插入数据失败
ERROR 1452 (23000): Cannot
add
or
update
a child row: a
foreign
key
constraint
fails (`market`.`user_order`,
CONSTRAINT
`user_order_ibfk_1`
FOREIGN
KEY
(`u_id`)
REFERENCES
`userprofile` (`id`)
ON
DELETE
CASCADE
ON
UPDATE
CASCADE
)
在上面的例子中,user_order表的外键约束,user_order表受userprofile表的约束
在user_order里面插入一条数据u_id为5用户,在userprofile表里面根本没有,所以插入数据失败
先向userprofile表中插入记录,再向user_order表中插入记录就可以了
MariaDB [market]>
insert
into
userprofile
values
(5,
"XJ"
,1); # 先向userprofile数据表中插入id为5的记录,插入数据成功
Query OK, 1 row affected (0.01 sec)
MariaDB [market]>
insert
into
user_order(u_id,username,money)
values
(5,
"XJ"
,345); # 再向user_order数据表中插入数据,成功
Query OK, 1 row affected (0.00 sec)
MariaDB [market]>
select
*
from
userprofile; # 查询userprofile数据表中的所有记录
+
----+------+-----+
| id |
name
| sex |
+
----+------+-----+
| 3 | HPC | 1 |
| 5 | XJ | 1 |
| 6 | LB | 2 |
+
----+------+-----+
3
rows
in
set
(0.00 sec)
MariaDB [market]>
select
*
from
user_order; # 查询user_order数据表中的所有记录
+
------+------+----------+-------+
| o_id | u_id | username | money |
+
------+------+----------+-------+
| 2 | 6 | LB | 146 |
| 3 | 3 | HPC | 256 |
| 5 | 5 | XJ | 345 |
+
------+------+----------+-------+
3
rows
in
set
(0.01 sec)
2.2 方法二:通过alter table创建外键和级联更新,级联删除
语法:
alter
table
数据表名称
add
[
constraint
[约束名称] ]
foreign
key
(外键字段,..)
references
数据表(参照字段,...)
[
on
update
cascade
|
set
null
|
no
action
]
[
on
delete
cascade
|
set
null
|
no
action
]
)
例子:
MariaDB [market]>
create
table
user_order1(o_id
int
(11) auto_increment,u_id
int
(11)
default
"0"
,username
varchar
(50),money
int
(11),
primary
key
(o_id),
index
(u_id)); # 创建user_order1数据表,创建表时不使用外键约束
Query OK, 0
rows
affected (0.11 sec)
MariaDB [market]> show
create
table
user_order1; # 查看user_order1数据表的创建信息,没有外键约束
+
-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Table
|
Create
Table
|
+
-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_order1 |
CREATE
TABLE
`user_order1` (
`o_id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`u_id`
int
(11)
DEFAULT
'0'
,
`username`
varchar
(50)
COLLATE
utf8_unicode_ci
DEFAULT
NULL
,
`money`
int
(11)
DEFAULT
NULL
,
PRIMARY
KEY
(`o_id`),
KEY
`u_id` (`u_id`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8
COLLATE
=utf8_unicode_ci |
+
-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row
in
set
(0.01 sec)
MariaDB [market]>
alter
table
user_order1
add
foreign
key
(u_id)
references
userprofile(id)
on
delete
cascade
on
update
cascade
; # 使用
alter
修改user_order1数据表,为user_order1数据表添加外键约束
Query OK, 0
rows
affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [market]> show
create
table
user_order1; # 查看user_order1数据表的创建信息,已经添加了外键约束
+
-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Table
|
Create
Table
|
+
-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_order1 |
CREATE
TABLE
`user_order1` (
`o_id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`u_id`
int
(11)
DEFAULT
'0'
,
`username`
varchar
(50)
COLLATE
utf8_unicode_ci
DEFAULT
NULL
,
`money`
int
(11)
DEFAULT
NULL
,
PRIMARY
KEY
(`o_id`),
KEY
`u_id` (`u_id`),
CONSTRAINT
`user_order1_ibfk_1`
FOREIGN
KEY
(`u_id`)
REFERENCES
`userprofile` (`id`)
ON
DELETE
CASCADE
ON
UPDATE
CASCADE
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8
COLLATE
=utf8_unicode_ci |
+
-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row
in
set
(0.00 sec)
3.删除外键
语法
alter
table
数据表名称
drop
foreign
key
约束(外键)名称
例子:
MariaDB [market]> show
create
table
user_order1; # 查看user_order1数据表的创建信息,包含外键约束
+
-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Table
|
Create
Table
|
+
-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_order1 |
CREATE
TABLE
`user_order1` (
`o_id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`u_id`
int
(11)
DEFAULT
'0'
,
`username`
varchar
(50)
COLLATE
utf8_unicode_ci
DEFAULT
NULL
,
`money`
int
(11)
DEFAULT
NULL
,
PRIMARY
KEY
(`o_id`),
KEY
`u_id` (`u_id`),
CONSTRAINT
`user_order1_ibfk_1`
FOREIGN
KEY
(`u_id`)
REFERENCES
`userprofile` (`id`)
ON
DELETE
CASCADE
ON
UPDATE
CASCADE
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8
COLLATE
=utf8_unicode_ci |
+
-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row
in
set
(0.00 sec)
MariaDB [market]>
alter
table
user_order1
drop
foreign
key
user_order1_ibfk_1; # 为user_order1数据表删除外键约束,外键名称必须与从`show
create
table
user_order1`语句中查到的相同
Query OK, 0
rows
affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [market]> show
create
table
user_order1; # 查看user_order1数据表的创建信息,外键约束已经被删除了
+
-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Table
|
Create
Table
|
+
-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_order1 |
CREATE
TABLE
`user_order1` (
`o_id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`u_id`
int
(11)
DEFAULT
'0'
,
`username`
varchar
(50)
COLLATE
utf8_unicode_ci
DEFAULT
NULL
,
`money`
int
(11)
DEFAULT
NULL
,
PRIMARY
KEY
(`o_id`),
KEY
`u_id` (`u_id`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8
COLLATE
=utf8_unicode_ci |
+
-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row
in
set
(0.00 sec)
4.使用外键约束的条件
要想外键创建成功,必须满足以下4个条件:
1、确保参照的表和字段存在。
2、组成外键的字段被索引。
3、必须使用type指定存储引擎为:innodb.
4、外键字段和关联字段,数据类型必须一致。
5.使用外键约束需要的注意事项
1.on delete cascade on update cascade 添加级联删除和更新:
2.确保参照的表userprofile中id字段存在。
3.确保组成外键的字段u_id被索引
4.必须使用type指定存储引擎为:innodb。
5.外键字段和关联字段,数据类型必须一致。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值。