深入mysql外鍵關聯問題的詳解

2024-07-24 12:48:24

字體：大中小

來源：轉載

供稿：網友

今兒繼續再看老師給推薦的深入淺出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），感覺這就是自己出問題的地方。

上一篇：使用use index優化sql查詢的詳細介紹

下一篇：mysql整數數據類型深入解析