深入mysql外鍵關(guān)聯(lián)問(wèn)題的詳解
更新時(shí)間：2013年06月14日 10:09:05 作者：
本篇文章是對(duì)mysql外鍵關(guān)聯(lián)問(wèn)題進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下
今兒繼續(xù)再看老師給推薦的深入淺出mysql數(shù)據(jù)庫(kù)開(kāi)發(fā)這本書(shū)，看到innodb數(shù)據(jù)庫(kù)的外鍵關(guān)聯(lián)問(wèn)題時(shí)，遇到了一個(gè)問(wèn)題，書(shū)上寫(xiě)的是可以對(duì)父表進(jìn)行修改，從而同步到子表的外鍵上去，可是自己的實(shí)驗(yàn)卻是沒(méi)有能夠。
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`))
上面的問(wèn)題是說(shuō)因?yàn)橛嘘P(guān)聯(lián)的存在，所以無(wú)法改變country_id這個(gè)字段。
然后自己又重新看了下書(shū)本，發(fā)現(xiàn)自己的sql語(yǔ)句中沒(méi)有innodb的外鍵約束方式（cascade,set null,no action,restrict），感覺(jué)這就是自己出問(wèn)題的地方。
可是怎么加入關(guān)聯(lián)方式呢，上網(wǎng)找了好半天也沒(méi)有合適的方法。就自己找唄，就通過(guò)老師說(shuō)的方法，？ help一點(diǎn)兒一點(diǎn)兒終于找到了怎么改變的方法，文檔功能很強(qiáng)大啊
| 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,...)
寫(xiě)了后又是一大堆的錯(cuò)誤，無(wú)從下手啊
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)-----我這里也建立索引了。網(wǎng)上的說(shuō)法是：字段類(lèi)型和外鍵的索引
這里是重新建立一張表icity，結(jié)果可以了，總結(jié)可能是因?yàn)樽侄晤?lèi)型的問(wèn)題，可是我的alter的問(wèn)題還是沒(méi)有解決呢：
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。呵呵……
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)
復(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 | 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 |
+------------+---------+---------------------+
復(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`))
上面的問(wèn)題是說(shuō)因?yàn)橛嘘P(guān)聯(lián)的存在，所以無(wú)法改變country_id這個(gè)字段。
然后自己又重新看了下書(shū)本，發(fā)現(xiàn)自己的sql語(yǔ)句中沒(méi)有innodb的外鍵約束方式（cascade,set null,no action,restrict），感覺(jué)這就是自己出問(wèn)題的地方。
可是怎么加入關(guān)聯(lián)方式呢，上網(wǎng)找了好半天也沒(méi)有合適的方法。就自己找唄，就通過(guò)老師說(shuō)的方法，？ 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,...)
寫(xiě)了后又是一大堆的錯(cuò)誤，無(wú)從下手啊
復(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: Remote I/O error
MySQL error code 121: Duplicate key on write or update
Can't create table 'test.icity' (errno: 150)-----我這里也建立索引了。網(wǎng)上的說(shuō)法是：字段類(lèi)型和外鍵的索引
這里是重新建立一張表icity，結(jié)果可以了，總結(jié)可能是因?yàn)樽侄晤?lèi)型的問(wèn)題，可是我的alter的問(wèn)題還是沒(méi)有解決呢：
復(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)
相關(guān)文章
這篇文章主要給大家介紹了關(guān)于MySQL DeadLock故障排查的全過(guò)程，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2019-01-01
Mysql row number()排序函數(shù)的用法和注意
這篇文章主要介紹了Mysql row number()排序函數(shù)的用法和注意 的相關(guān)資料,非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友可以參考下2016-07-07
這篇文章主要介紹了MySQL 查看事務(wù)和鎖情況的常用語(yǔ)句分享，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
Mysql使用sum()函數(shù)返回null的問(wèn)題詳解
MySQL的Sum()函數(shù)是用來(lái)找出所有記錄的一個(gè)字段的總和,下面這篇文章主要給大家介紹了關(guān)于Mysql使用sum()函數(shù)返回null的問(wèn)題,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-01-01
Mysql?安裝及my.ini的創(chuàng)建過(guò)程
這篇文章主要介紹了Mysql?安裝及my.ini的創(chuàng)建過(guò)程，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-07-07
淺談MySQL數(shù)據(jù)查詢(xún)太多會(huì)OOM嗎
本文主要介紹了淺談MySQL數(shù)據(jù)查詢(xún)太多會(huì)OOM嗎？文中通過(guò)示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-08-08
MySQL到Kafka實(shí)時(shí)數(shù)據(jù)同步
很多 DBA 同學(xué)經(jīng)常會(huì)遇到要從一個(gè)數(shù)據(jù)庫(kù)實(shí)時(shí)同步到另一個(gè)數(shù)據(jù)庫(kù)的問(wèn)題,同構(gòu)數(shù)據(jù)還相對(duì)容易,遇上異構(gòu)數(shù)據(jù)、表多、數(shù)據(jù)量大等情況就難以同步,我自己親測(cè)了一種方式,可以非常方便的實(shí)現(xiàn)MySQL Kafka實(shí)時(shí)數(shù)據(jù)同步,需要的朋友可以參考下2024-01-01
ERROR 1862 (HY000): Your password has expired. To log in you
當(dāng)你在安裝 MySQL過(guò)程中，通過(guò)mysqld --initialize 初始化 mysql 操作后，生成臨時(shí)密碼后，沒(méi)有直接進(jìn)行 MySQL連接，中途重啟服務(wù)或者重啟機(jī)器等，導(dǎo)致密碼失效問(wèn)題，怎么處理呢，感興趣的朋友一起看看吧2019-11-11
這篇文章介紹了防止web項(xiàng)目中的SQL注入，小編覺(jué)得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。需要的朋友可以收藏下，方便下次瀏覽觀看2021-12-12
本文主要介紹了不卸載原有mysql直接安裝mysql8.0，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01