mysql的外键作用

创建两个表

第一个表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>