快捷導(dǎo)航

深入mysql外鍵關(guān)聯(lián)問題的詳解

更新時(shí)間：2013年06月14日 10:09:05 作者：

本篇文章是對(duì)mysql外鍵關(guān)聯(lián)問題進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下

今兒繼續(xù)再看老師給推薦的深入淺出mysql數(shù)據(jù)庫開發(fā)這本書，看到innodb數(shù)據(jù)庫的外鍵關(guān)聯(lián)問題時(shí)，遇到了一個(gè)問題，書上寫的是可以對(duì)父表進(jìn)行修改，從而同步到子表的外鍵上去，可是自己的實(shí)驗(yàn)卻是沒有能夠。

復(fù)制代碼代碼如下:

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&nbsp;&nbsp;&nbsp;&nbsp; | country_id | last_update&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
+---------+----------+------------+---------------------+
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | hancheng |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | 2012-01-09 09:18:33 |
+---------+----------+------------+---------------------+
1 row in set (0.01 sec)
mysql> select * from country;
+------------+---------+---------------------+
| country_id | country | last_update&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
+------------+---------+---------------------+
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | chen&nbsp;&nbsp;&nbsp; | 2012-01-09 09:16:38 |
+------------+---------+---------------------+

復(fù)制代碼代碼如下:

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`))

上面的問題是說因?yàn)橛嘘P(guān)聯(lián)的存在，所以無法改變country_id這個(gè)字段。
然后自己又重新看了下書本，發(fā)現(xiàn)自己的sql語句中沒有innodb的外鍵約束方式（cascade,set null,no action,restrict），感覺這就是自己出問題的地方。
可是怎么加入關(guān)聯(lián)方式呢，上網(wǎng)找了好半天也沒有合適的方法。就自己找唄，就通過老師說的方法，？ help一點(diǎn)兒一點(diǎn)兒終于找到了怎么改變的方法，文檔功能很強(qiáng)大啊

復(fù)制代碼代碼如下:

| 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,...)

寫了后又是一大堆的錯(cuò)誤，無從下手啊

復(fù)制代碼代碼如下:

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:&nbsp; Remote I/O error
MySQL error code 121: Duplicate key on write or update
&nbsp;
Can't create table 'test.icity' (errno: 150)-----我這里也建立索引了。網(wǎng)上的說法是：字段類型和外鍵的索引

這里是重新建立一張表icity，結(jié)果可以了，總結(jié)可能是因?yàn)樽侄晤愋偷膯栴}，可是我的alter的問題還是沒有解決呢：

復(fù)制代碼代碼如下:

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)

在大家（老師和網(wǎng)友）的幫助下終于搞定了，做法先drop掉表里的外鍵，然后在add。呵呵……

復(fù)制代碼代碼如下:

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)