(编辑：jimmy 日期: 2024/12/30 浏览：2)
主键与外键的关系，通俗点儿讲，我现在有一个论坛，有两张表，一张是主贴 thread，一张是回帖 reply
先说说主键，主键是表里面唯一识别记录的字段，一般是帖子id，体现在访问的时候，例如是
thread.php"htmlcode">
CREATE TABLE `dage` ( `id` int(11) NOT NULL auto_increment, `name` varchar(32) default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1； 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=latin1；
插入个大哥：
mysql> insert into dage(name) values('铜锣湾');
Query OK, 1 row affected (0.01 sec)
mysql> select * from dage;
+----+--------+ | id | name | +----+--------+ | 1 | 铜锣湾 | +----+--------+ 1 row in set (0.00 sec)
插入个小弟：
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');
2ERROR 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了呢！
例子说明的应该蛮清楚了吧，其他功能对应手册自己实践吧！:-)