MariaDB数据库的外键约束实例详解_mariadb

来源:脚本之家  责任编辑:小易  

外键是该表是另一个表之间联接的字段外键必须为另一个表中的主键外键的用途是确保数据的完整性。它通常包括以下几种:实体完整性,确保每个实体是唯一的(通过主键来实施)域完整性,确保属性值只从一套特定可选的集合里选择关联完整性,确保每个外键或是NULL(如果允许的话)或含有与相关主键值相配的值例子:表A 字典表,表B 业务表。外键应建立在表B 上。1、表B 的字典项目的代码只能是表A 中代码内容。2、表B 可以无条件删除记录。3、表A 在删除的时候,将根据外键的规则,判断表B 中是否使用了要删除数据的代码,如果有引用,则不能删除。ORACLE 如何建立表外键例:学生表student(id,name,sex)成绩表score(id,math)如何创建表,要求 有主键,有约束 解:CREATE TABLE STUDENT(ID CHAR(10),NAME VARCHAR(8),SEX CHAR(1));ALTER TABLE STUDENT ADD CONSTRAINT PK_STUDENT PRIMARY KEY(ID);CREATE TABLE SCORE(ID CHAR(10),MATH NUMBER(5,2));ALTER TABLE SCORE ADD CONSTRAINT FK_SCROE FOREIGN KEY(ID)REFERENCES STUDENT(ID);主键与外键:键是表中的列(可以是一列,也可以是几列),主键用于唯一的标识表中的数据项;外键用于连e69da5e6ba90e799bee5baa631333337623535接父表和子表。而所谓的父表和子表是根据3NF 范式的要求,为了消除传递依赖,将原表拆成2个相互关联的表,而这个关联就是外键www.zgxue.com防采集请勿采集本网。

外键

外键的用途是确保数据的完整性。它通常包括以下几种:

2、外键(foreign key):一张表(答关系)内的列(属性)它同时存在表1和表2中,它不是表1的主键,而是表2的主键,就可以说他是表1的外键。3、候选键容(Candidate Key):能唯一标识表(关系)中行(元组)

1 实体完整性,确保每个实体是唯一的(通过主键来实施)

你在建立外键的时候要指定更新的关联,这样删除User中的一笔记录,所有以User表的UserId为外键的表的记录都会删除,修改User中UserId的值,所有所有以User表的UserId为外键的表的记录都会更新这个

2 域完整性,确保属性值只从一套特定可选的集合里选择

alter table 表 add constraint 外键约束名称 foreign key(列)references 表(主键列);

3 关联完整性,确保每个外键或是NULL(如果允许的话)或含有与相关主键值相配的值

外键的用途是确保数据的完整性。它通常包括以下几种: 实体完整性,确保每个实体是唯一的(通过主键来实施) 域完整性,确保属性值只从一套特定可选的集合里选择 关联完整性,确保每个外键或是NULL(如果

1.什么是外键约束

先在S表里添加S2的记录 在P表里,添加P4的记录 在J表里添加J6的记录 最后才能在SPJ里添加你那条记录 前三个顺序可以随意调换,但是最后一条必须在满足上述三个的情况下才可以添加 不知道你的添加

与主键约束不同,创建外键约束不会自动创建对应的索引。 但是由于以下原因,对外键手动创建索引通常是有用的: 当在查询中组合相关表中的数据时,经常在联接条件中使用外键列,方法是将一个表的外键约束中的一列或多列与另一个表中的主键列或唯一键列匹配。 索引使 数据库引擎 可以在外键表中快速查找相关数据。 但是,创建此索引并不是必需的。 即使没有对两个相关表定义主键或外键约束,也可以对来自这两个表中的数据进行组合,但两个表间的外键关系说明已用其键作为条件对其进行了优化,以便组合到查询中。 对主键约束的更改可由相关表中的外键约束检查。

外键约束(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 changedMariaDB [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: 0MariaDB [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: 0MariaDB [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为6Query 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: 0MariaDB [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: 0MariaDB [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.外键字段和关联字段,数据类型必须一致。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对真格学网的支持。

说明send和accept都是user表的外键。constraint f_key foreign key(send)references user(username);constraint f_key2 foreign key(accept)references user(username);对于messagebox主键表就是本身嘛,外键表自然就是user表了即user表是messagebox表的父表内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • 浅谈mysql和mariadb区别
  • centos 7安装mysql5.5和安装 mariadb使用的命令
  • centos7 下mysql重新启动mariadb篇
  • mac中mariadb数据库的安装步骤
  • mariadb的主从复制、主主复制、半同步复制配置详解
  • centos安装和设置mariadb的教程
  • 关于mariadb安装问题小记(cmake error at)
  • 记一次mariadb数据库无法连接
  • centos 7中成功安装mariadb的方法教程
  • mariadb性能调优工具mytop的使用详解
  • mariadb数据库的外键约束实例详解
  • centos 7中成功安装mariadb的方法教程
  • 关于mongoose连接mongodb重复访问报错的解决办法
  • centos 7安装mysql5.5和安装 mariadb使用的命令
  • centos安装和设置mariadb的教程
  • centos中找回mariadb数据库root用户权限的方法
  • window7安装mariadb数据库及系统初始化操作分析
  • exchange在接收连接器上启用匿名中继的方法
  • windows10系统下安装mariadb 的教程图解
  • mariadb的主从复制、主主复制、半同步复制配置详解
  • 数据库外键约束。
  • 数据库中的外键约束
  • 数据库表中有外键,该怎么添加数据?
  • 数据库建表时一个表最多可以有几个主键,几个外键
  • 数据库里面的主键和外键及候选键是什么意思啊?
  • 如何修改数据库中主外键约束的数据
  • 表已创建好了,想添加外键约束,代码怎么写
  • 想问一下数据库设置外键约束的主要目的
  • SQL中怎么向有外键约束的表中添加数据
  • oracle数据库里,主键约束里的主键怎么理解?外键呢?举例说明一下
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mariadb浅谈mysql和mariadb区别centos 7安装mysql5.5和安装 mariadb使用的命令centos7 下mysql重新启动mariadb篇mac中mariadb数据库的安装步骤mariadb的主从复制、主主复制、半同步复制配置详解centos安装和设置mariadb的教程关于mariadb安装问题小记(cmake error at)记一次mariadb数据库无法连接centos 7中成功安装mariadb的方法教程mariadb性能调优工具mytop的使用详解mariadb数据库的外键约束实例详解centos 7中成功安装mariadb的方法教程关于mongoose连接mongodb重复访问报错的解决办法centos 7安装mysql5.5和安装 mariadb使用的命令centos安装和设置mariadb的教程centos中找回mariadb数据库root用户权限的方法window7安装mariadb数据库及系统初始化操作分析exchange在接收连接器上启用匿名中继的方法windows10系统下安装mariadb 的教程图解mariadb的主从复制、主主复制、半同步复制配置详解浅谈mysql和mariadb区别centos 7安装mysql5.5和安装 marcentos7 下mysql重新启动mariadbmariadb中1045权限错误导致拒绝用mac中mariadb数据库的安装步骤mysql分支选择参考:percona还是详解centos 使用yum安装mariadbmariadb的主从复制、主主复制、半记一次mariadb数据库无法连接centos安装和设置mariadb的教程windows10系统下安装mariadb 的教程图解centos7 下mysql重新启动mariadb篇mariadb性能调优工具mytop的使用详解关于mongoose连接mongodb重复访问报错的解mariadb中的thread pool详细介绍和使用方mac中mariadb数据库的安装步骤windows server 2016 服务器配置指南 之 mysql分支选择参考:percona还是mariadbcentos 7中成功安装mariadb的方法教程mariadb的主从复制、主主复制、半同步复制
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved