mysql外键入门引见

2012-07-04

mysql外键入门介绍CREATE?TABLE?`dage`?(?2??`id`?int(11)?NOT?NULL?auto_increment,?3??`name`?varchar(3

mysql外键入门介绍
CREATE?TABLE?`dage`?(
?2 mysql外键入门引见 ??`id`?int(11)?NOT?NULL?auto_increment,
?3??`name`?varchar(32)?default?'',
?4??PRIMARY?KEY??(`id`)
?5)?ENGINE=InnoDB?DEFAULT?CHARSET=latin1；
?6
?7CREATE?TABLE?`xiaodi`?(
?8??`id`?int(11)?NOT?NULL?auto_increment,
?9??`dage_id`?int(11)?default?NULL,
10??`name`?varchar(32)?default?'',
11 mysql外键入门引见 ??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；
插入个大哥：
1mysql>?insert?into?dage(name)?values('铜锣湾');
2Query?OK,?1?row?affected?(0.01?sec)
3 mysql外键入门引见 mysql>?select?*?from?dage;
4+----+--------+
5|?id?|?name???|
6+----+--------+
7|??1?|?铜锣湾?|
8+----+--------+
91?row?in?set?(0.00?sec)
插入个小弟：
1mysql>?insert?into?xiaodi(dage_id,name)?values(1,'铜锣湾_小弟A');
2Query?OK,?1?row?affected?(0.02?sec)
3
4mysql>?select?*?from?xiaodi;
5 mysql外键入门引见 +----+---------+--------------+
6|?id?|?dage_id?|?name?????????|
7+----+---------+--------------+
8|??1?|???????1?|?铜锣湾_小弟A?|
9+----+---------+--------------+
把大哥删除：
1mysql>?delete?from?dage?where?id=1;
2ERROR?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');??????????????
2 mysql外键入门引见

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`))
3 mysql外键入门引见

提示：小子，想造反呀！你还没大哥呢！

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

mysql>?show?create?table?xiaodi;
?2 mysql外键入门引见

??CONSTRAINT?`xiaodi_ibfk_1`?FOREIGN?KEY?(`dage_id`)?REFERENCES?`dage`?(`id`)
?4 mysql外键入门引见

mysql>?alter?table?xiaodi?drop?foreign?key?xiaodi_ibfk_1;?
?6 mysql外键入门引见

Query?OK,?1?row?affected?(0.04?sec)
?7 mysql外键入门引见

Records:?1??Duplicates:?0??Warnings:?
?8 mysql外键入门引见

mysql>?alter?table?xiaodi?add?foreign?key(dage_id)?references?dage(id)?on?delete?cascade?on?update?cascade;
?9 mysql外键入门引见

Query?OK,?1?row?affected?(0.04?sec)
10 mysql外键入门引见

Records:?1??Duplicates:?0??Warnings:?0
再次试着把大哥删了：
1 mysql外键入门引见

mysql>?delete?from?dage?where?id=1;
2 mysql外键入门引见

Query?OK,?1?row?affected?(0.01?sec)
3 mysql外键入门引见

mysql>?select?*?from?dage;
5 mysql外键入门引见

Empty?set?(0.01?sec)
6 mysql外键入门引见

mysql>?select?*?from?xiaodi;
8 mysql外键入门引见

Empty?set?(0.00?sec)

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

例子说明的应该蛮清楚了吧，其他功能对应手册自己实践吧！:-)

热点排行

Mysql

mysql外键入门引见