深入mysql外键关联问题的详解
程序员文章站
2024-02-21 21:31:43
今儿继续再看老师给推荐的深入浅出mysql数据库开发这本书，看到innodb数据库的外键关联问题时，遇到了一个问题，书上写的是可以对父表进行修改，从而同步到子表的外键上去，...
今儿继续再看老师给推荐的深入浅出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这个字段。
然后自己又重新看了下书本，发现自己的sql语句中没有innodb的外键约束方式（cascade,set null,no action,restrict），感觉这就是自己出问题的地方。
可是怎么加入关联方式呢，上网找了好半天也没有合适的方法。就自己找呗，就通过老师说的方法，？ help一点儿一点儿终于找到了怎么改变的方法，文档功能很强大啊
| add {index|key} [index_name] [index_type] (index_col_name,...)
| add [constraint [symbol]]
primary key [index_type] (index_col_name,...)
| add [constraint [symbol]]
unique [index|key] [index_name] [index_type] (index_col_name,...)
写了后又是一大堆的错误，无从下手啊
mysql> alter table city add constraint `city_ibfk_1` foreign key (`country_id`) references `country` (`country_id`) on update cascade;
error 1005 (hy000): can't create table '.\test\#sql-ed0_37.frm' (errno: 121)
zhouqian@zhou:~$ perror 121
os error code 121: remote i/o error
mysql error code 121: duplicate key on write or update
can't create table 'test.icity' (errno: 150)-----我这里也建立索引了。网上的说法是：字段类型和外键的索引
这里是重新建立一张表icity，结果可以了，总结可能是因为字段类型的问题，可是我的alter的问题还是没有解决呢：
mysql> create table icity(id int not null, city varchar(20), country_id smallint unsigned not null , primary key(id), foreign key(country_id) references country(country_id) on update cascade )engine=innodb;
query ok, 0 rows affected (0.11 sec)
mysql> show create table icity\g
*************************** 1. row ***************************
table: icity
create table: create table `icity` (
`id` int(11) not null,
`city` varchar(20) default null,
`country_id` smallint(5) unsigned not null,
primary key (`id`),
key `country_id` (`country_id`),
constraint `icity_ibfk_1` foreign key (`country_id`) references `country` (`country_id`) on update cascade
) engine=innodb default charset=latin1
1 row in set (0.02 sec)
在大家（老师和网友）的帮助下终于搞定了，做法先drop掉表里的外键，然后在add。呵呵……
mysql> alter table city drop foreign key `city_ibfk_1`;
query ok, 0 rows affected (0.24 sec)
records: 0 duplicates: 0 warnings: 0
mysql> alter table city add foreign key (`country_id`) references `country` (`country_id`) on update cascade;query ok, 0 rows affected (0.16 sec)
records: 0 duplicates: 0 warnings: 0
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`),
key `idx_fk_country_id` (`country_id`),
constraint `city_ibfk_1` foreign key (`country_id`) references `country` (`country_id`) on update cascade
) engine=innodb default charset=utf8
1 row in set (0.00 sec)
复制代码 代码如下:
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这个字段。
然后自己又重新看了下书本，发现自己的sql语句中没有innodb的外键约束方式（cascade,set null,no action,restrict），感觉这就是自己出问题的地方。
可是怎么加入关联方式呢，上网找了好半天也没有合适的方法。就自己找呗，就通过老师说的方法，？ help一点儿一点儿终于找到了怎么改变的方法，文档功能很强大啊
复制代码 代码如下:
| add {index|key} [index_name] [index_type] (index_col_name,...)
| add [constraint [symbol]]
primary key [index_type] (index_col_name,...)
| add [constraint [symbol]]
unique [index|key] [index_name] [index_type] (index_col_name,...)
写了后又是一大堆的错误，无从下手啊
复制代码 代码如下:
mysql> alter table city add constraint `city_ibfk_1` foreign key (`country_id`) references `country` (`country_id`) on update cascade;
error 1005 (hy000): can't create table '.\test\#sql-ed0_37.frm' (errno: 121)
zhouqian@zhou:~$ perror 121
os error code 121: remote i/o error
mysql error code 121: duplicate key on write or update
can't create table 'test.icity' (errno: 150)-----我这里也建立索引了。网上的说法是：字段类型和外键的索引
这里是重新建立一张表icity，结果可以了，总结可能是因为字段类型的问题，可是我的alter的问题还是没有解决呢：
复制代码 代码如下:
mysql> create table icity(id int not null, city varchar(20), country_id smallint unsigned not null , primary key(id), foreign key(country_id) references country(country_id) on update cascade )engine=innodb;
query ok, 0 rows affected (0.11 sec)
mysql> show create table icity\g
*************************** 1. row ***************************
table: icity
create table: create table `icity` (
`id` int(11) not null,
`city` varchar(20) default null,
`country_id` smallint(5) unsigned not null,
primary key (`id`),
key `country_id` (`country_id`),
constraint `icity_ibfk_1` foreign key (`country_id`) references `country` (`country_id`) on update cascade
) engine=innodb default charset=latin1
1 row in set (0.02 sec)
在大家（老师和网友）的帮助下终于搞定了，做法先drop掉表里的外键，然后在add。呵呵……
复制代码 代码如下:
mysql> alter table city drop foreign key `city_ibfk_1`;
query ok, 0 rows affected (0.24 sec)
records: 0 duplicates: 0 warnings: 0
mysql> alter table city add foreign key (`country_id`) references `country` (`country_id`) on update cascade;query ok, 0 rows affected (0.16 sec)
records: 0 duplicates: 0 warnings: 0
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`),
key `idx_fk_country_id` (`country_id`),
constraint `city_ibfk_1` foreign key (`country_id`) references `country` (`country_id`) on update cascade
) engine=innodb default charset=utf8
1 row in set (0.00 sec)