服务器之家

服务器之家 > 正文

深入mysql外键关联问题的详解

时间:2019-12-29 16:43     来源/作者:MYSQL教程网

今儿继续再看老师给推荐的深入浅出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)

 
标签:

相关文章

热门资讯

玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
配置IIS网站web服务器的安全策略配置解决方案
配置IIS网站web服务器的安全策略配置解决方案 2019-05-23
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
Nginx服务器究竟是怎么执行PHP项目
Nginx服务器究竟是怎么执行PHP项目 2019-05-24
返回顶部