您的位置:金沙游乐场85155 > 大数据库 > MariaDB数据库的外键约束实例详解

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

发布时间:2020-03-16 01:58编辑:大数据库浏览(78)

    1、确保参照的表和字段存在。 2、组成外键的字段被索引。 3、必须使用type指定存储引擎为:innodb.4、外键字段和关联字段,数据类型必须一致。

    mysql数据对象,mysql对象

      学习目标:


     

    • 了解掌握常见的几种数据库对象
    • 学会如何创建具体的数据对象

      mysql 常见的数据对象有哪些:

    • DataBase/Schema
    • Table
    • Index
    • View/Trigger/Function/Procedure

      多Database用途:

    • 业务的隔离
    • 资源的隔离

      表上的常用数据对象:

    • 索引
    • 约束
    • 视图,触发器,函数,存储过程

        索引


      什么是数据库索引:            索引就是数据库中数据的目录:            索引和数据时两个对象            索引主要是用来提高数据库的查询效率            数据库中数据变更同样需要同步索引数据的变更,                 因为索引是按照B+TREE,排好位置的,一旦数据变化,则这个数据的相应位置也要变化,                 这样之后再查找,才能快速索引到,而变化位置就是索引的维护;   查看某个数据库命令:  

    mysql> help create index
    Name: 'CREATE INDEX'
    Description:
    Syntax:
    CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
        [index_type]
        ON tbl_name (index_col_name,...)
        [index_option] ..
    

    UNIQUE:唯一索引(用户表的ID,手机号等,唯一信息) SPATIAL:地理位置索引(搜索周边有哪些人)   两种创建索引及查看表中索引的方法

    mysql> select * from vc;
    +------+------+
    | v    | c    |
    +------+------+
    | AB   | AB   |
    +------+------+
    1 row in set (0.00 sec)
    
    mysql> create index idx_v on vc(v);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> alter table vc add KEY idx_c (c);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> show index from vc;
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | vc    |          1 | idx_v    |            1 | v           | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
    | vc    |          1 | idx_c    |            1 | c           | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    2 rows in set (0.00 sec)
    
    mysql> show create table vc;
    +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                   |
    +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | vc    | CREATE TABLE `vc` (
      `v` varchar(5) DEFAULT NULL,
      `c` char(5) DEFAULT NULL,
      KEY `idx_v` (`v`),
      KEY `idx_c` (`c`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    

    在mysql中 INDEX 和KEY 都代表了索引;     约束


      约束: 唯一约束: unique 外键约束: CONSTRAINT   创建唯一约束(唯一索引可以促成唯一约束):

    • 唯一约束是一种特殊的索引
    • 唯一约束可以是一个或者多个字段
    • 位于约束可以在建表的时候建好,也可以后面补上
    • 主键也是一种唯一约束

    金沙游乐场85155 1 金沙游乐场85155 2

     

      索引有哪些:

    • 主键索引  ID
    • 单键索引  orderid
    • 单键索引  bookid
    • 组合索引 (userid + orderid)

                         唯一约束有哪些:

    • 主键约束           ID
    • 单键唯一索引     orderid
    • 组合唯一索引     userid+orderid

      添加唯一约束 添加主键索引: 

    mysql> alter table order add primary key (id)
    #实际上是给主键id增加了一个索引,而这个索引又是唯一的所以就这个索引就变成了唯一约束
    

    添加唯一索引:

    mysql>alter table order add unique key idx_uk_orderid(id)
    

      外键约束 将两张表的数据通过某种条件关联起来 例子:买东西,订单表,用户信息表; 如果没有外键约束:可能存在不存在用户也能下单成功; 而如果使用外键约束,则在数据库层面是的不存在的用户不能下单成功。   创建外键约束: 将用户表和订单表通过外键关联起来:

    mysql > alter table order add CONSTRAINT constarint_uid FOREIGN KEY (userid) REFERENCES user(userid)
    

    创建外键约束的时候,可以指定在删除,更新父表(被参照的表)时,对子表(做外键关联的表) 进行的相应操作,包括可:restrict(限制),cascade(串联),set null 和 no action。 其中restrict和 no action 相同,是指限制在子表有关联记录的情况下,父表不能更新; cascade表示父表在更新或删除时,更新或者删除子表对应的记录; set null 表示父表在更新或者删除时子表的对应字段被set null。 选择后两种方式的时候要谨慎,可能会因为错误的操作导致数据的丢失。     在执行LOAD DATA和 ALTER TABLE 操作的时候,为了加快速度,可以暂时关闭外键约束: set foreign key_checks =0;等完成之后再开启:set foreign key_checks =1  

    mysql > alter table order add CONSTRAINT constarint_uid FOREIGN KEY (userid) REFERENCES user(userid) on delete restrict on update cascade;
    

      on update cascade例子: 

    mysql> select * from c_A;
    +----+------+
    | id | age  |
    +----+------+
    |  1 |   22 |
    |  2 |    3 |
    |  3 |    4 |
    +----+------+
    3 rows in set (0.00 sec)
    
    mysql> select * from c_B;
    +----+------+
    | id | age  |
    +----+------+
    |  1 |    2 |
    |  2 |   33 |
    |  3 |    4 |
    +----+------+
    3 rows in set (0.00 sec)
    
    mysql> update c_B set id=11 where age = 2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from c_B;
    +----+------+
    | id | age  |
    +----+------+
    |  2 |   33 |
    |  3 |    4 |
    | 11 |    2 |
    +----+------+
    3 rows in set (0.00 sec)
    
    mysql> select * from c_A;
    +----+------+
    | id | age  |
    +----+------+
    |  2 |    3 |
    |  3 |    4 |
    | 11 |   22 |
    +----+------+
    3 rows in set (0.00 sec)
    

    注意:外键约束更改的只是约束的那个字段;   on delete cascade  

    on delete cascade insert update delete
    parent yes 只能更改子表中约束字段没有的值 yes
    child 只能插入父表中约束字段有的值; 只能更改父表中约束字段没有的值  yes

            on update cascade  

    on update cascade insert update delete
    parent yes yes 只能删子表中约束字段没有的值;
    child 只能插入父表中约束字段有的值; 只能更新父表中约束字段没有的值  yes

            可以见child只能删除,其余都受限余父表的约束字段; 而parent都能插入,但其中一条受限于子表约束字段;     给order表增加了一个约束,(CONSTRAINT,约束的关键词)约束名 constarint_uid; 他是一个外键约束且order表中userid是外键,把order表中的userid 关联 到  user表的userid;   使用外键的注意事项:

    • 必须是innodb表,其他引擎不支持外键
    • 相互约束的字段类型必须要一样
    • 主表的约束字段要求有索引(上面的例子中,user表就是主表,所以在user表中要userid要求加上索引 )
    • 约束名称必须要唯一,即使不在一张表上(constarint_uid 在整个库中是唯一的)

      删除一个约束:

    mysql> alter table order drop FOREIGN KEY constarint_uid;
    

     

    视图view


      view作用:

    • 视图将一组查询语句构成的结果集,是一种虚拟结构,并不是实际数据
    • 视图能简化数据库的访问,能够将多个查询语句结构化为一个虚拟结构
    • 视图可以隐藏数据库后端表结构,提高数据库的安全性
    • 视图也是一种权限管理,只对用户提供部分数据

      创建一个已完成订单的视图:

    mysql > create view order_view as select * from order where status = 1
    

      视图例子:

    mysql> CREATE TABLE t (qty INT, price INT);
    mysql> INSERT INTO t VALUES(3, 50);
    mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
    mysql> SELECT * FROM v;
    +------+-------+-------+
    | qty  | price | value |
    +------+-------+-------+
    |    3 |    50 |   150 |
    +------+-------+-------+
    

     

    触发器


      触发器 是什么: 触发器是加在表上的特殊程序,当表上出现特定的事件 (insert/update/delete/alter table)时触发该程序执行。   对一个表的一个事件mysql中只能定义一个触发器   做什么:

    • 数据订正;
    • 迁移表;
    • 实现特定的业务逻辑;

      触发器有两个用户:

    • 执行者
    • 调用者  

      触发器--基本语法

    CREATE
    [DEFINER = {user | CURRENT_USER}]     -- 定义执行着的权限
    TRIGGER trigger_name trigger_time
    trigger_event ON tbl_name
    FOR EACH ROW                                    --涉及的每一行都会执行trigger_body
    trigger_body  t
    
    trigger_time:{BEFORE | AFTER}
    trigger_event:{INSERT | UPDATE | DELETE}
    

      delimiter 结束符的设定,默认是分号。但是在触发器应为要执行一组SQL,会出现分号所以讲结束符 设定一下; 金沙游乐场85155 3.png)   金沙游乐场85155 4

     

    用 NEW,OLD来引用触发器中发生变化的记录内容   触发触发器的顺序: before 触发器,行操作,after 触发器;其中任何一个步骤操作发生错误都不会继续执行剩下的操作。 如果是对事务表进行的操作,那么会整个作为一个事务被回滚(rollback),但是如果是对非事务表进行的操作 ,那么已经更新的记录将无法回滚,这也是设计触发器的时候需要注意得问题。     触发器的特点:

    • 触发器对性能有损耗,应慎用
    • 同一类事件在一个表中只能创建一次
    • 对于事务表,触发器执行失败则整个语句回滚
    • row格式主从复制,触发器不会在从属库上执行
    • 使用触发器时应该防止递归执行

      可以协助应用在数据库端确保数据的完整性。       存储过程


      定义:存储过程是存储在数据库端的一组SQL语句集,用户可以通过存储过程名和传参多次调用的程序模块。   特点:

    • 使用灵活,可以使用流控制语句,自定义变量等完成复杂的业务逻辑
    • 提高数据安全性,屏蔽应用程序直接对表的操作,易于进行审计
    • 减少网络传输
    • 提高代码维护的复杂度,实际使用中要评估场景是否适合

      存储过程--基本语法

    CREATE 
         [ DEFINER = { user | CURRENT_USER } ]                 --定义执行着的权限
         PROCEDURE  sp_name ( [ proc_parameter[ ,... ] ] )
         [ characteristic .. ]  routine_body
    
    proc_parameter:
         [ IN | OUT | INOUT]  param_name  type
                                       type:
                                                Any valid MySQL data type
    

     

    IN:输入参数,表示该参数值在调用存储过程中已经指定好了,在调用存储过程中修改该参数不会返回 OUT:输出参数,可以在存储过程内部被改变,可以返回 INOUT:输入输出参数;   characteristic:      COMMENT 'string'                    --注释      | [NOT] DETERMINISTIC          --是否会返回确定值   routine_body:      Valid SQL routine statement    --与trigger类似   金沙游乐场85155 5.png) 金沙游乐场85155 6

     

        重点:

    • 中间变量定义:DECLARE
    • 流控制语句
    • 参数传入

      查询:数据库中有哪些存储过程

    mysql> show PROCEDURE STATUS ;
    

    查看:某个触发器的详情

    mysql> SHOW TRIGGER STATUS ;
    

     

    存储过程的使用: 1.设置参数值:

    mysql> set @total = 5;
    mysql> set @res = 0;
    

    2.调用存储过程:

    mysql> call proc_test1(@total,@res);
    

    3.查看返回值:

    mysql> select @res
    

      删除存储过程或者函数:

    DROP {PROCEDURE | FUNCTION}  [IF  EXISTS]  sp_name
    

      查看存储过程或者函数 SHOW {procedure | function}  status like ‘file_in_stock’   查看存储过程或者函数的定义 show  create {procedure |  function}  sp_name   存储过程-流控制语言 金沙游乐场85155 7.png)  金沙游乐场85155 8

     

      自定义函数

    • 自定义函数与存储过程类似,但是必须带有返回值
    • 自定义函数与sum(),max(),等mysql原生函数使用方法类似: select func(val); select * from tbl where col = func(val)
    • 由于自定义函数可能在遍历数据中使用,要注意性能损耗

      自定义函数-基本语法 金沙游乐场85155 9.png)  金沙游乐场85155 10 最终通过RETURN;  金沙游乐场85155 11   调用

    mysql> select func_test1(4);
    

      确认权限: 创建存储过程或者函数需要:create  routine 修改或者删除存储过程需要:alter  routine 执行存储过程 需要 :           execute    可以嵌套使用;   小结


     

    • 触发器和存储过程不利于水平扩展,多用于统计和运维操作中;
    • 还有代码管理维护成本比较高;
    • 但是:
      • 简化应用开发,减少数据传输,提高处理效率;

       总结


    •  索引的创建于查看
    •  约束:
      •   唯一约束,
      • 外键约束:
        • alter table order add CONSTRAINT constarint_uid FOREIGN KEY (userid) REFERENCES user(userid);
        • 外键约束四种模式:restrict,no action,set null,cascade;
    •  VIEW
    •  TIGGER:
      • 一个表的一个事件只能定义一个触发器
      • delimiter //
      • NEW.age  OLD.age
    •  PROCEDURE:
      • DECLARE,
      • 参数传入返回
      • 流控制语言
      • 使用三步骤
        • 初始化参数
        • 调用存储过程
        • 查看放回值
    •  function:
      • 必须带有返回值
      • 使用select func_test1(14);
    •  SUBSTRING( goods_name,1,5 ):从位置1截取goods_name5个字符
    •  A rigth join B on..:B显示B中null字段;

     

    http://www.bkjia.com/Mysql/1143056.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1143056.htmlTechArticlemysql数据对象,mysql对象 学习目标: 了解掌握常见的几种数据库对象 学会如何创建具体的数据对象 mysql 常见的数据对象有哪些: DataBase/Sc...

    4.使用外键约束的条件

    精简化后的语法:

    1 实体完整性,确保每个实体是唯一的

    2.1.4 测试数据完整性

    总结

    更新数据

    2.1.3 测试级联更新

    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)
    

    语法:

    1.什么是外键约束

    2.1.1 插入测试数据

    金沙游乐场85155,例子:

    语法:

    通过外键约束,每次插入或更新数据表时,都会检查数据的完整性。

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

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

    3.删除外键

    5.使用外键约束需要的注意事项

    关于数据表的完整性和关连性,可以举个例子

    RESTRICT: 拒绝对父表的删除或更新操作。CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用

    要想外键创建成功,必须满足以下4个条件:

    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)
    
    foreign key 当前表的字段 references 外部表名 (关联的字段) type=innodb 
    

    注意:on update cascade是级联更新的意思,on delete cascade是级联删除的意思,意思就是说当你更新或删除主键表,那外键表也会跟随一起更新或删除。

    2.1 方法一:通过create table创建外键

    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] 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)
    
    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)
    

    本文由金沙游乐场85155发布于大数据库,转载请注明出处:MariaDB数据库的外键约束实例详解

    关键词: