MySQL外键关联问题
MySQL外键关联问题
继续再看老师给推荐的深入浅出mysql数据库开发这本书(下载见 ),看到innodb数据库的外键关联问题时,遇到了一个问题,书上写的是可以对父表进行修改,从而同步到子表的外键上去,可是自己的实验却是没有能够。
- mysql> show create table country\G
- *************************** 1. row ***************************
- Table: country
- Create Table: CREATE TABLE `country` (
- `country_id` smallint(5) unsigned NOT NULL auto_increment,
- `country` varchar(50) NOT NULL,
- `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
- PRIMARY KEY (`country_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 1 row in set (0.01 sec)
- mysql> show create table city\G
- *************************** 1. row ***************************
- Table: city
- Create Table: CREATE TABLE `city` (
- `city_id` smallint(5) unsigned NOT NULL auto_increment,
- `city` varchar(50) NOT NULL,
- `country_id` smallint(5) unsigned NOT NULL,
- `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
- PRIMARY KEY (`city_id`),
- KEY `country_id` (`country_id`),
- CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
- mysql> select * from city;
- +---------+----------+------------+---------------------+
- | city_id | city | country_id | last_update |
- +---------+----------+------------+---------------------+
- | 1 | hancheng | 1 | 2012-01-09 09:18:33 |
- +---------+----------+------------+---------------------+
- 1 row in set (0.01 sec)
- mysql> select * from country;
- +------------+---------+---------------------+
- | country_id | country | last_update |
- +------------+---------+---------------------+
- | 1 | chen | 2012-01-09 09:16:38 |
- +------------+---------+---------------------+
- mysql> update country set country_id=100 where country_id=1;
- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/city`, CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`))
上面的问题是说因为有关联的存在,所以无法改变country_id这个字段。
|
评论暂时关闭