精华内容
下载资源
问答
  • MySQL InnoDB外键

    2013-03-06 11:00:40
    MySQL InnoDB外键2008-10-15 00:15:07 分类: Mysql/postgreSQL 从mysql 3.23.44开始,innodb支持外键约束,跟Oracle基本相同,语法形式如下 [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) ...
    MySQL InnoDB外键
    2008-10-15 00:15:07 
    

    分类: Mysql/postgreSQL

    mysql 3.23.44开始,innodb支持外键约束,Oracle基本相同,语法形式如下

    [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

    REFERENCES tbl_name (index_col_name, ...)

    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

     

    ALTER TABLE yourtablename

    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

    REFERENCES tbl_name (index_col_name, ...)

    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

     

     

    外键约束须满足以下条件:

     . 父子表都为innodb,不是临时表

     

     . 在子表上,必须存在一个索引,外键列是索引列的全部或部分,但必须是开头部分,并且顺序一致;mysql4.1.2开始,如果不存在索引,会自动创建外键上的索引;加速约束检查/避免全表扫描

     

     . 在父表上,必须存在一个索引,被参照键是索引列的全部或部分,但必须是开头部分,并且顺序一致;加速约束检查/避免全表扫描

     

     . 不允许在外键前缀或后缀上索引;外键不能包括text/blob

     

     . 如果指定约束标识符 symbol,那么必须数据库范围唯一; 不指定时,系统会自动产生

     

     . 父子表的相应列必须有相似的内部数据类型,以便进行比较时不必进和类型转换

     

       对于数字类型,类型长度与符号必须相同;

       对于字符类型,长度不必相同

     

     . 如果创建一个on delete set nullon update set null约束,子表的列必须不能为not null

     

     . mysql3.23.50开始,如果外键或候选键(被引用键)列上有null值,mysql将不进行check

     

     

    外键约束对子表的含义:

      如果在父表中找不到候选键,则不允许在子表上进行insert/update

     

    外键约束对父表的含义:

      在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于:在定义子表的外键时指定的on update/on delete子句, InnoDB支持5种方式,分列如下

     

      . cascade方式

       在父表上update/delete记录时,同步update/delete掉子表的匹配记录

       On delete cascademysql3.23.50开始可用; on update cascademysql4.0.8开始可用

     

      . set null方式

       在父表上update/delete记录时,将子表上匹配记录的列设为null

       要注意子表的外键列不能为not null

       On delete set nullmysql3.23.50开始可用; on update set nullmysql4.0.8开始可用

     

      . No action方式

       如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

       这个是ANSI SQL-92标准,mysql4.0.8开始支持

     

      . Restrict方式

       no action,都是立即检查外键约束

     

      . Set default方式

       解析器认识这个action,Innodb不能识别,不知道是什么意思...

     

      注意:trigger不会受外键cascade行为的影响,即不会解发trigger

     

    mysql中,与SQL标准相违背的三点

    1.       如果在父表中有多个key值相同,那么在进行外键check,会当成有相同key值的其他行不存在;比如当定义了一个restrict行为外键时,一个子表行对应到多个父表行(具有相同key), Innodb不允许删除父表上的所有这些行

     

    下面这句未理解,depth-first?

    InnoDB performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints.

     

    2.       父子表是同一个表,自我参照时不允许指定on update cascade, on update set null

    mysql4.0.13开始,允许同一个表上的on delete set null

    mysql4.0.21开始,允许同一个表上的on delete cascade

    但级联层次不能超出15

     

    3, Innodb在检查unique,constraint约束时,是row by row而不是语句或事务结束;

      SQL标准中对constraint的检查是在语句执行完成时

     

     

    创建/操作外键的例子

    --简单外键

    CREATE TABLE parent

    (

     id INT NOT NULL,

     PRIMARY KEY (id)

    )

    engine=INNODB;

     

    CREATE TABLE child

    (

       id INT,

       parent_id INT,

       INDEX par_ind (parent_id,id), è外键列是索引列的开头部分

       FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE

    )

    engine=INNODB;

     

    --较复杂的外键

    CREATE TABLE product

    (

       category INT NOT NULL,

       id INT NOT NULL,

       price DECIMAL,

       PRIMARY KEY(category, id)

    )

    engine=INNODB;

     

    CREATE TABLE customer

    (

      id INT NOT NULL,

      PRIMARY KEY (id)

    )

    engine=INNODB;

     

    CREATE TABLE product_order

    (

       no INT NOT NULL AUTO_INCREMENT,

       product_category INT NOT NULL,

       product_id INT NOT NULL,

       customer_id INT NOT NULL,

       PRIMARY KEY(no),

       INDEX (product_category, product_id),

       FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT,

       INDEX (customer_id),

       FOREIGN KEY (customer_id)REFERENCES customer(id)

    )

    engine=INNODB;

     

    --查看外建/drop外键/alter添加外键

    mysql> show create table child\G

    *************************** 1. row ***************************

           Table: child

    Create Table: CREATE TABLE `child` (

      `id` int(11) default NULL,

      `parent_id` int(11) default NULL,

      KEY `par_ind` (`parent_id`,`id`),

      CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE

    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    1 row in set (0.00 sec)

     

    mysql> alter table child drop FOREIGN KEY child_ibfk_1; 

    Query OK, 0 rows affected (0.01 sec)

    Records: 0  Duplicates: 0  Warnings: 0

     

    mysql> alter table child add constraint child_ibfk_1

        -> FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE;

    Query OK, 0 rows affected (0.01 sec)

    Records: 0  Duplicates: 0  Warnings: 0

     

     

    Mysql中与外键相关的错误

     

    If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message

    string refers to errno 150, this means that the table creation failed because a foreign key constraint

    was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to errno 150, that

    means a foreign key definition would be incorrectly formed for the altered table. Starting from

    MySQL 4.0.13, you can use SHOW INNODB STATUS to display a detailed explanation of the

    latest InnoDB foreign key error in the server.

     

    测试如下:

    set FOREIGN_KEY_CHECKS = 0; --禁用约束检查

    drop table product;  --删除product_order的父表

    set FOREIGN_KEY_CHECKS = 1; --启用约束检查

    重新创建product,这次没有建pk

    CREATE TABLE product

    (

       category INT NOT NULL,

       id INT NOT NULL,

       price DECIMAL,

       index(category, id)

    )

    engine=INNODB;

     

    查看show innodb status中的错误信息

    ------------------------

    LATEST FOREIGN KEY ERROR

    ------------------------

    080424 19:38:50  Cannot drop table `test/product`

    because it is referenced by `test/product_order`

     

    其它

    1, mysqldumpdump数据时,会加入外键信息

     

    2, 也可以用SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';查看外键信息,comment列中

     

    3, 注意innodb中如果设置了lower_case_table_names,对外键的影响

     

    4, 在创建外键时,可以加入backtricks(反引号),将外键名,列名包括起来

     

    5, 如果是在mysql3.23.50之前版本,注意如果表上有外键或者作为父表被refer,不要使用alter table , create index命令

     

    6, Innodb要进行外键check时,需要在父或子表上加共享行级锁(shared row lock),不是事务级

     

    7, mysql4.1.1开始,为了使reload dumpfile更容易进行,mysqldump生成的文件自动添加了FOREIGN_KEY_CHECKS=0选项以禁止外键约束检查

      对于早期版本,可以用下面方法达到同样目的

       mysql> SET FOREIGN_KEY_CHECKS = 0;

    mysql> SOURCE dump_file_name

    mysql> SET FOREIGN_KEY_CHECKS = 1;

     

    8, alter tableload data时,可能也需要进行上述设置以临时禁止外键约束检查

     

    9, 除非设置了FOREIGN_KEY_CHECKS = 0, Innodb禁止drop父表(被子表外键引用的表)

     

    10, 当设置FOREIGN_KEY_CHECKS = 0drop一个父子.

      下次重新创建这个父表时,必须跟drop之前一样(正确的列/数据类型/被参照的列上必须有索引),否则系统会报1005 refer to errno 150  (这一点前述测试中没有发现报错)

    展开全文
  • innodb外键限制

    千次阅读 2015-05-27 21:12:23
    innodb permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first colu

    定义上:

    innodb permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
    允许参照一个索引列或者一组列,一组列的第一个必须是索引。


    InnoDB does not currently support foreign keys for tables with user-defined partitioning. This means that no user-partitioned InnoDB table may contain foreign key references or columns referenced by foreign keys.
    innodb不支持用户定义的分区表,即分区的innodb表中不能有外键或者被其他键参照。


    InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.
    innodb允许参照一个非唯一索引。


    总的来说innodb的外键参照可以是主键、索引、非唯一索引

    Referenti

    展开全文
  • mysql innodb 外键

    2013-09-18 11:40:50
     在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于:在定义子表的外键时指定的on update/on delete子句, InnoDB支持5种方式, 分列如下    . cascade方式  
    外键约束对子表的含义:
    
      如果在父表中找不到候选键,则不允许在子表上进行insert/update

    外键约束对父表的含义:
      在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于:在定义子表的外键时指定的on update/on delete子句, InnoDB支持5种方式, 分列如下
     
      . cascade方式
       在父表上update/delete记录时,同步update/delete掉子表的匹配记录
       On delete cascade从mysql3.23.50开始可用; on update cascade从mysql4.0.8开始可用

      . set null方式
       在父表上update/delete记录时,将子表上匹配记录的列设为null
       要注意子表的外键列不能为not null
       On delete set null从mysql3.23.50开始可用; on update set null从mysql4.0.8开始可用

      . No action方式
       如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
       这个是ANSI SQL-92标准,从mysql4.0.8开始支持

      . Restrict方式
       同no action, 都是立即检查外键约束

      . Set default方式
       解析器认识这个action,但Innodb不能识别,不知道是什么意思...
     
      注意:trigger不会受外键cascade行为的影响,即不会解发trigger
    展开全文
  • 使用MySQL开发过程中需要对锁的知识理解清楚,不然在业务代码中就有可能产生死锁,尤其是要知道Innodb使用外键的时候的锁机制,才能更好的避免生产环境发生死锁,造成严重bug。 参考 Mysql中那些锁机制之...

    最近项目中突然发现一次锁现象,订单多次付款,最后一次退款。退款完成后支付系统手动第三方回调,支付系统多次通知订单系统,订单系统在这个过程中发生死锁,下面给出订单系统表结构做模拟死锁。

    情景

    • 数据库结构(5.7.13)
     
    1. create database test_deadlock default character set utf8 collate utf8_general_ci;

    2.  
    3. use test_deadlock;

    4.  
    5. create table db_order(

    6. id bigint(1) not null auto_increment comment '主键',

    7. order_no varchar(64) not null comment '订单号',

    8. order_status tinyint(4) not null default '1' comment '订单状态',

    9. create_date timestamp not null default current_timestamp comment '开单时间',

    10. primary key (id)

    11. )engine=innodb default charset =utf8;

    12.  
    13. create table db_payment(

    14. id bigint not null auto_increment comment '主键',

    15. order_id bigint(1) not null comment '订单主表id',

    16. payment_amount decimal(19,2) not null default '0' comment '支付金额',

    17. primary key (id)

    18. )engine=innodb default charset =utf8;;

    19.  
    20. alter table db_payment add constraint fk_order_id foreign key(order_id) references db_order(id);

    21. 复制代码

    • 初始化数据
     
    1. insert into db_order(order_no,order_status) values ('10001',5);

    2. insert into db_payment(order_id, payment_amount) values (1,100);

    3. 复制代码

    • 第一个事务
     
    1. start transaction;

    2. insert into db_payment(order_id, payment_amount) values (1,100);

    3. update db_order set order_status=6 where id=1;

    4. commit ;

    5. 复制代码

    • 第二个事务
     
    1. start transaction;

    2. insert into db_payment(order_id, payment_amount) values (1,200);

    3. update db_order set order_status=7 where id=1;

    4. commit ;

    5. 复制代码

    模拟

    为了方便模拟,这个使用idea连接数据库分别打开两个console,并且开启Manual模式。

    • 事务A

     

     

     

    • 事务B

     

     

     

    这里我们使用TA(1)表示执行第一个事务的第一行代码。首先我们执行TA(1)和TA(2),然后执行TB(1),TB(2),然后在执行TA(3),再执行TB(3),此时得到结构如下。

     
    1. [40001][1213] Deadlock found when trying to get lock; try restarting transaction

    2. 复制代码

    可以看出InnoDB检测到死锁。

    接下来我们删除外键,执行操作

     
    1. alter table db_payment drop foreign key fk_order_id;

    2. 复制代码

    然后再次执行上面的操作,操作过程分别问TA(1),TA(2),TB(1),TB(2),TA(3),TB(3),TA(4),TB(4),最后两个事务都完成执行。

    可以看出一个有外键和一个没有外键的区别。

    分析原因

    • 核心知识点 为了理解上文中死锁的原因,必须要理解清楚Innodb的锁的机制,MySQL锁的机制文章很多,可以去官网找文档或者阅读他人的博客,这里给出一篇博客快速了解innodb锁概念MySQL InnoDB自增长锁和外键锁以便于我们理解本文中的死锁问题。

    • 分析

       

    我们用这一张图分析完为什么死锁,在第5步和第6步的时候发生了相互等待,Innodb在TB中检查到了死锁,反过来思考,加入数据库删除了外键,在第2步我第3步做insert db_payment操作的时候都没有对db_order id=1的这行数据加入S锁,那么就没有步骤5对步骤4的S锁等待,显然这个执行过程只有步骤6对步骤5等待,TAcommit之后,TB就会获得锁执行下一步commit。

    总结

    使用MySQL开发过程中需要对锁的知识理解清楚,不然在业务代码中就有可能产生死锁,尤其是要知道Innodb使用外键的时候的锁机制,才能更好的避免生产环境发生死锁,造成严重bug。

    参考

    展开全文
  • 可通过查表信息查看外键的创建信息:...从mysql 3.23.44开始,innodb支持外键约束,跟Oracle基本相同,语法形式如下 [CONSTRAINTsymbol] FOREIGN KEY [id] (index_col_name, ...) REFERENCEStbl_name (index_col_name
  • 今天create table 和 alter table 添加外建的时候发现,我指定外键为 RESTRICT,我用show create table 没法看到有RESTRICT,后来实验证明RESTRICT 和no action 效果是一致的,都是对父表的update/delete 立即检查,...
  • innodb支持外键,但是由于外键,也会对innodb表增加锁定机制假设一个表为parent,一个表为child,parent通过id和child的parent_id相连接;在一个session中set autocommit=0,执行对parent或child的操作,在另一个...
  • MYSQL INNODB 关联的表必须都是innodb <br />在已曾在的表中创建外键 alter table 外键所在的表名 add constraint 外键名 foreign key(外键所在的列名) references 对应主键所在表名(主键所在...
  • 首先保证父表和子表都是innodb 而不是临时表 1/当父表删除子表对应外键值也对应删除 2/当子表更新父表没有的数据会报错,禁止添加insert和更新update操作 父表 create table tblmaster ( id int not null auto_...
  • CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。... SET NULL: 从父表删除或更新行,并设置子表中的外键列为NULL。如果外键列没有指定NOT NULL限定词,这就是唯一合法的。ON DELETE S...
  • MySQL建表时MyISAM和InnoDB外键冲突解决

    千次阅读 2011-07-19 16:28:07
    在MySQL中,InnoDB类型的表中用外键引用MyISAM表中的主键,在建表时会遇到错误! 例如,创建用户表users create table users( id int not null primary key auto_increment, username
  • 使用innodb支持外键

    2013-05-16 13:47:00
    2019独角兽企业重金招聘Python...可以看到MyISAM是默认的engine,它是不支持foreign key的,使用外键需要在创建表时指定engine=innodb; 转载于:https://my.oschina.net/soitravel/blog/131261
  • MySQL的MyISAM是不支持外键的,InnoDB支持外键外键是MySQL中的三大约束中的一类:主键约束(PRIMARY KEY),唯一性约束(UNIQUE),外键约束(FOREIGN KEY)。 2. 约束语法 主键:[CONSTRAINT [symbol]] PRIMARY...
  • 我建了两个表person和shirt其中shirt有一个owner字段是外键,引用的是person的主键id表示shirt的所有者是person中的一个row于是有如下操作:两个表:person+-------+----------------------+------+-----+---------+---...
  • Innodb引擎的外键约束

    2015-03-19 14:58:56
    MySQL 支持外键的存储引擎只有 InnoDB, 在创建外键的时候, 要求父表必须有对应的 索引 , 子表在创建外键的时候也会自动创建对应的索引 。 下面是样例数据库中的两个表, country 表是父表,...
  • MySQL/InnoDB外键约束(Foreign Key Constraint) SQL外键约束 一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。 FOREIGN KEY 约束用于预防破坏表之间连接的动作。 FOREIGN KEY 约束也能...
  • InnoDB存储引擎 外键约束 语法: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) REFERENCES tbl_name(index_col_name,...) ON DELETE ...
  • InnoDB存储引擎 外键约束语法:CREATE[TEMPORARY]TABLE[IFNOTEXISTS]tbl_name[CONSTRAINT[symbol]]FOREIGNKEY[index_name](index_col_name,...)REFERENCEStbl_name(index_col_name,...)ONDELETE[RESTRICT|CA...
  • InnoDB存储引擎中,对于一个外键列,如果没有显示地对这个列加索引,InnoDB存储引擎会自动对其加一个索引,因为这样可以避免表锁。 这比Oracle数据库做得好,Oracle数据库不会自动添加索引,用户必须自己手动添加...
  • # ---------------------------------------# Host : localhost# Port : 3306# Database : chhoSET FOREIGN_KEY_CHECKS=0;DROP DATABASE IF EXISTS `chho`;CREATE DATABASE `chho`;USE `chho`;...
  • 1.只有InnoDB引擎才允许使用外键,所以,我们的数据表必须使用InnoDB引擎。 2.注意:1、必须使用InnoDB引擎;2、外键必须建立索引(INDEX);3、外键绑定关系这里使用了“ONDELETECASCADE”,意思是如果外键对应...
  • In order to set up a foreign key constraint with InnoDB (under the "Relation View" link on the Structure tab) it appears that I need to add an index for the field to which I wan...
  • 外键约束

    千次阅读 2016-07-07 21:11:17
    约束: 约束保证数据的完整性和一致性 约束分为表级约束和列级约束 约束类型包括:NOT NULL...数据表的存储引擎必须为InnoDB 外键列与参照列必须具有相似的数据类型,其中数字的长度或有无符号位必须相同,而字符的长度
  • InnoDB foreign keys and MySQL partitioning are not compatible....这么遗憾的局限,数据量大的时候,分区很必要,关系需要约束时,外键也很必要。 想知道高手们在碰到这类问题是如何解决的?难道分区后用触发器?

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 51,464
精华内容 20,585
关键字:

innodb外键