• MySQL外键的介绍


    在MySQL 3.23.44版本后,InnoDB引擎类型的表支持了外键约束。

    1、MySQL中“键”和“索引”的定义相同,所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引,但是外键字段必须由用户进行明确的索引。用于外键关系的字段必须在所有的参照表中进行明确地索引,InnoDB不能自动地创建索引。

    2、外键可以是一对一的,一个表的记录只能与另一个表的一条记录连接,或者是一对多的,一个表的记录与另一个表的多条记录连接。

    3、如果需要更好的性能,并且不需要完整性检查,可以选择使用MyISAM表类型,如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能,最好选择表结构为innoDB类型。

    4、外键的使用条件

       两个表必须是InnoDB表,MyISAM表暂时不支持外键

       外键列必须建立了索引,MySQL 4.1.2以后的版本在建立外键时会自动创建索引,但如果在较早的版本则需要显式建立;

       外键关系的两个表的列必须是数据类型相似,也就是可以相互转换类型的列,比如inttinyint可以,而intchar则不可以;

    5、外键的好处:可以使得两张表关联,保证数据的一致性和实现一些级联操作。

    外键的定义语法:
    [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
        REFERENCES tbl_name (index_col_name, ...)
        [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
        [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

    "[]"中的参数表示可选;

    "|"代表或

    该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用,如果不指定CONSTRAINT symbol,MYSQL会自动生成一个名字。

    ON DELETE、ON UPDATE表示在删除和更新事件触发时限制,可以设置参数:

    RESTRICT(限制外表中的外键改动)
    CASCADE(跟随外键改动)
    SET NULL(设空值)
    SET DEFAULT(设默认值)
    NO ACTION(无动作,默认的)

    搞个例子,简单演示一下使用,做dage和xiaodi两个表,大哥表是主键,小弟表是外键:
    建表:

     1 CREATE TABLE `dage` (
     2    `id` int(11) NOT NULL auto_increment,
     3    `name` varchar(32) default '',
     4     PRIMARY KEY  (`id`)
     5  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
     6  
     7  CREATE TABLE `xiaodi` (
     8    `id` int(11) NOT NULL auto_increment,
     9    `dage_id` int(11) default NULL,
    10   `name` varchar(32) default '',
    11   PRIMARY KEY  (`id`),
    12   KEY `dage_id` (`dage_id`),
    13   CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
    14 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    插入个大哥:

    1 mysql> insert into dage(name) values('铜锣湾');
    2 Query OK, 1 row affected (0.01 sec)
    3 mysql> select * from dage;
    4 +----+--------+
    5 | id | name   |
    6 +----+--------+
    7 |  1 | 铜锣湾 |
    8 +----+--------+
    9 1 row in set (0.00 sec)

    插入个小弟:

    1 mysql> insert into xiaodi(dage_id,name) values(1,'铜锣湾_小弟A');
    2 Query OK, 1 row affected (0.02 sec)
    3 
    4 mysql> select * from xiaodi;
    5 +----+---------+--------------+
    6 | id | dage_id | name         |
    7 +----+---------+--------------+
    8 |  1 |       1 | 铜锣湾_小弟A |
    9 +----+---------+--------------+

    把大哥删除:

    1 mysql> delete from dage where id=1;
    2 ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`bstar/xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

    提示:不行呀,有约束的,大哥下面还有小弟,可不能扔下我们不管呀!

    插入一个新的小弟:

    1 mysql> insert into xiaodi(dage_id,name) values(2,'旺角_小弟A');              
    2 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bstar/xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

    提示:小子,想造反呀!你还没大哥呢!

    把外键约束增加事件触发限制:

     1 mysql> show create table xiaodi;
     2  
     3   CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
     4  
     5  mysql> alter table xiaodi drop foreign key xiaodi_ibfk_1; 
     6  Query OK, 1 row affected (0.04 sec)
     7  Records: 1  Duplicates: 0  Warnings: 
     8  mysql> alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;
     9  Query OK, 1 row affected (0.04 sec)
    10 Records: 1  Duplicates: 0  Warnings: 0

    再次试着把大哥删了:

    1 mysql> delete from dage where id=1;
    2 Query OK, 1 row affected (0.01 sec)
    3 
    4 mysql> select * from dage;
    5 Empty set (0.01 sec)
    6 
    7 mysql> select * from xiaodi;
    8 Empty set (0.00 sec)

    得,这回对应的小弟也没了,没办法,谁让你跟我on delete cascade了呢!

    Mysql的外键不能直接从当前的数据表中查看,所有的外键都定义在数据库information_schema的KEY_COLUMN_USAGE表中,如果想要查找某个表的外键,可以用以下语句:

    1 SELECT * FROM `KEY_COLUMN_USAGE` WHERE `REFERENCED_TABLE_NAME` = 'your_table_name'

    结果集部分字段解释:

    TABLE_SCHEMA:外键所在的数据库

    TABLE_NAME:外键所在的数据表

    COLUMN_NAME:建立外键的字段

    REFERENCED_TABLE_SCHEMA:外键引用的数据库

    REFERENCED_TABLE_NAME外键引用的数据表

    REFERENCED_COLUMN_NAME:外键引用的字段

    删除外键:

    1 alter table <table_name> drop foreign key <foreign_key_name>

    添加外键:

    1 ALTER TABLE <table_name> ADD CONSTRAINT <foreign_key_name> FOREIGN KEY ( <column_name> ) REFERENCES <reference_table_name> ( <reference_column_name> ) ON DELETE CASCADE ON UPDATE CASCADE ;

    必须注意:reference_column_name必须是主键!!!!!

     (本文大部分转载自http://www.cppblog.com/wolf/articles/69089.html)

  • 相关阅读:
    javaweb:Filter过滤器
    javaScript:高级
    javascript:基础
    Boostrao:轮播图
    Bootstrap案列:首页界面
    Bootstrap学习笔记
    javaweb:respone
    javaweb:jsp
    五、结构型模式--->07.享元模式
    五、结构型模式--->06.组合模式
  • 原文地址:https://www.cnblogs.com/mityaya/p/4439393.html
Copyright © 2020-2023  润新知