mysql外键关联不上_mysql外键关联问题

今儿继续再看老师给推荐的深入浅出mysql开发这本书，看到innodb数据库的外键关联问题时，遇到了一个问题，书上写的是可以对父表进行修改，从而同步到子表的外键上去，可是自己的实验却是没有能够。 show create table country\G ***************************

今儿继续再看老师给推荐的深入浅出mysql开发这本书，看到innodb数据库的外键关联问题时，遇到了一个问题，书上写的是可以对父表进行修改，从而同步到子表的外键上去，可是自己的实验却是没有能够。

> 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)-----我这里也建立索引了。网上的说法是：字段类型和外键的索引

作者 辉蛋儿的学习历程