MySQL外键设置详解
(1) 外键的使用:
外键的作用,主要有两个:
其实这正式外键在强制你保证数据的完整性和一致性.这是好事儿.
(2) 添加外键的格式:
ALTER TABLE yourtablename
说明:
on delete/on update,用于定义delete,update操作.以下是update,delete操作的各种约束类型:
CASCADE:
外键表中外键字段值会被更新,或所在的列会被删除.
RESTRICT:
RESTRICT也相当于no action,即不进行任何操作.即,拒绝父表update外键关联列,delete记录.
set null:
被父面的外键关联字段被update ,delete时,子表的外键列被设置为null.
而对于insert,子表的外键列输入的值,只能是父表外键关联列已有的值.否则出错.
外键定义服从下列情况:(前提条件)
1)
所有tables必须是InnoDB型,它们不能是临时表.因为在mysql中只有InnoDB类型的表才支持外键.
2)
所有要建立外键的字段必须建立索引.
3)
对于非InnoDB表,FOREIGN KEY子句会被忽略掉。
注意:
创建外键时,定义外键名时,不能加引号.
如: constraint 'fk_1' 或 constraint "fk_1"是错误的
(3) 查看外键:
SHOW CREATE TABLE ***;可以查看到新建的表的代码以及其存储引擎.也就可以看到外键的设置.
删除外键:
alter table drop foreign key '外键名'.
注意:
只有在定义外键时,用constraint 外键名 foreign key .... 方便进行外键的删除.
若不定义,则可以:
先输入:alter table drop foreign key -->会提示出错.此时出错信息中,会显示foreign key的系统默认外键名.--->
用它去删除外键.
(4) 举例
简单演示一下使用,做dage和xiaodi两个表,大哥表是主键,小弟表是外键:
建表:
CREATE TABLE `dage` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(32) default '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
CREATE TABLE `xiaodi` (
`id` int(11) NOT NULL auto_increment,
`dage_id` int(11) default NULL,
`name` varchar(32) default '',
PRIMARY KEY (`id`),
KEY `dage_id` (`dage_id`),
CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
插入个大哥:
mysql> insert into xiaodi(dage_id,name) values(1,'铜锣湾_小弟A');
Query OK, 1 row affected (0.02 sec)
mysql> select * from xiaodi;
+----+---------+--------------+
| id | dage_id | name |
+----+---------+--------------+
| 1 | 1 | 铜锣湾_小弟A |
+----+---------+--------------+
插入个小弟:
mysql> insert into xiaodi(dage_id,name) values(1,'铜锣湾_小弟A');
Query OK, 1 row affected (0.02 sec)
mysql> select * from xiaodi;
+----+---------+--------------+
| id | dage_id | name |
+----+---------+--------------+
| 1 | 1 | 铜锣湾_小弟A |
+----+---------+--------------+
把大哥删除:
mysql> delete from dage where id=1;
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`))
提示:不行呀,有约束的,大哥下面还有小弟,可不能扔下我们不管呀!
插入一个新的小弟
mysql> insert into xiaodi(dage_id,name) values(2,'旺角_小弟A');
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`))
提示:小子,想造反呀!你还没大哥呢!
把外键约束增加事件触发限制:
mysql> show create table xiaodi;
CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
mysql> alter table xiaodi drop foreign key xiaodi_ibfk_1;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings:
mysql> alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
说明:
mysql> delete from dage where id=1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from dage;
Empty set (0.01 sec)
mysql> select * from xiaodi;
Empty set (0.00 sec)
得,这回对应的小弟也没了,没办法,谁让你跟我on delete cascade了呢!
例子说明的应该蛮清楚了吧,其他功能对应手册自己实践吧!:-)
参考文章
http://www.cppblog.com/wolf/articles/69089.html