创建两个表
第一个表dage
CREATE TABLE `dage` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
第二个表xiaodi,该表的dage_id是以dage的id为主键的外键
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`) REF
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
此时如果我们添加一个小弟会报错
mysql> insert into xiaodi values(1,1,'www');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REF
ERENCES `dage` (`id`))
因为dage没有id=1的lie,如果有就,可以成功插入
mysql> insert into dage values(1,1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into xiaodi values(1,1,'www');
Query OK, 1 row affected (0.01 sec)
mysql> select*from xiaodi where dage_id=1;
+----+---------+------+
| id | dage_id | name |
+----+---------+------+
| 1 | 1 | www |
+----+---------+------+
1 row in set (0.00 sec)
mysql>
此时我们想删除小弟不会对大哥产生影响,但是删除dage表中有小弟的类,就删不了,必须先没有小弟才能删除大哥
mysql> delete from dage where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`)
REFERENCES `dage` (`id`))
mysql> delete from xiaodi where dage_id=1;
Query OK, 1 row affected (0.01 sec)
mysql> delete from dage where id=1;
Query OK, 1 row affected (0.01 sec)
mysql>