mysql外键关联问题
来源:互联网 发布:秘密软件下载 编辑:程序博客网 时间:2024/06/04 19:55
今儿继续再看老师给推荐的深入浅出mysql数据库开发这本书,看到innodb数据库的外键关联问题时,遇到了一个问题,书上写的是可以对父表进行修改,从而同步到子表的外键上去,可是自己的实验却是没有能够。
mysql> show create table country\G*************************** 1. row *************************** Table: countryCreate 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=utf81 row in set (0.01 sec)mysql> show create table city\G*************************** 1. row *************************** Table: cityCreate 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=utf81 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 121OS error code 121: Remote I/O errorMySQL 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: icityCreate 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=latin11 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: 0mysql> 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: 0mysql> show create table city\G*************************** 1. row *************************** Table: cityCreate 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=utf81 row in set (0.00 sec)
- mysql外键关联问题
- mysql 外键关联中遇到的问题
- 深入mysql外键关联问题的详解(上)
- 深入mysql外键关联问题的详解(中)
- 深入mysql外键关联问题的详解(下)
- mysql 外键关联限制
- mysql设计外键关联
- MySQL忽略外键关联
- MySql数据库外键关联
- myql 外键关联问题
- mysql多库表关联问题症结
- 关于linux下使用phpmyadmin给mysql表关联外键的问题和解决办法
- 关于linux下使用phpmyadmin给mysql表关联外键的问题和解决办法
- mysql innodb 外键关联约束实例
- 使用navicat创建MySQL外键关联
- MYSQL 建立外键关联错误
- mysql 设置外键关联错误 150
- mysql innodb 外键关联约束讲解
- Dictionary<TKey, TValue>.ContainsKey 方法 不区分大小写
- 获得手机root权限
- Linux下Wine软件的安装
- CentOS yum的详细使用方法
- ORA-19909:当进行不完全恢复之后使用open database resetlogs
- mysql外键关联问题
- 编译ics遇到的问题
- StandardServer.await: create[8005]java.net.BindException: Address already in use: JVM_Bind
- pthread_cleanup_push/pop
- 淘宝数据魔方技术架构解析
- java相关1
- HP-UX prompt 命令 查看历史记录
- 指针与指针的引用
- 利用 squid 反向代理提高网站性能(转摘)