快速理解MySQL中主键与外键的实例教程

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了呢！

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

您可能感兴趣的文章:

MySQL 创建主键,外键和复合主键的语句MySQL 主键与索引的联系与区别分析MySQL中的主键以及设置其自增的用法教程sqlserver数据库主键的生成方式小结(sqlserver,mysql)mysql把主键定义为自动增长标识符类型Oracle与Mysql主键、索引及分页的区别小结Mysql主键相关的sql语句集锦MySQL索引之主键索引MySQL中主键为0与主键自排约束的关系详解(细节)