首页 / MYSQL / MySQL外键关联(创世纪新篇)
MySQL外键关联(创世纪新篇)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL外键关联(创世纪新篇),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含8113字,纯文字阅读大概需要12分钟。
内容图文
![MySQL外键关联(创世纪新篇)](/upload/InfoBanner/zyjiaocheng/1139/b91519d07cb6412188fdcc4f488141db.jpg)
数据库外键
-
01.mysql> show create table country\G
02.*************************** 1. row ***************************
03. Table: country
04.Create Table: CREATE TABLE `country` (
05. `country_id` smallint(5) unsigned NOT NULL auto_increment,
06. `country` varchar(50) NOT NULL,
07. `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
08. PRIMARY KEY (`country_id`)
09.) ENGINE=InnoDB DEFAULT CHARSET=utf8
10.1 row in set (0.01 sec)
11.
12.mysql> show create table city\G
13.*************************** 1. row ***************************
14. Table: city
15.Create Table: CREATE TABLE `city` (
16. `city_id` smallint(5) unsigned NOT NULL auto_increment,
17. `city` varchar(50) NOT NULL,
18. `country_id` smallint(5) unsigned NOT NULL,
19. `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
20. PRIMARY KEY (`city_id`),
21. KEY `country_id` (`country_id`),
22. CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`)
23.) ENGINE=InnoDB DEFAULT CHARSET=utf8
24.1 row in set (0.00 sec)
25.mysql> select * from city;
26.+---------+----------+------------+---------------------+
27.| city_id | city | country_id | last_update |
28.+---------+----------+------------+---------------------+
29.| 1 | hancheng | 1 | 2012-01-09 09:18:33 |
30.+---------+----------+------------+---------------------+
31.1 row in set (0.01 sec)
32.
33.mysql> select * from country;
34.+------------+---------+---------------------+
35.| country_id | country | last_update |
36.+------------+---------+---------------------+
37.| 1 | chen | 2012-01-09 09:16:38 |
38.+------------+---------+---------------------+
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 |
+------------+---------+---------------------+
01.mysql> update country set country_id=100 where country_id=1;
02.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`))
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一点儿一点儿终于找到了怎么改变的方法,文档功能很强大啊
01.| ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
02. | ADD [CONSTRAINT [symbol]]
03. PRIMARY KEY [index_type] (index_col_name,...)
04. | ADD [CONSTRAINT [symbol]]
05. UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
| 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,...)
写了后又是一大堆的错误,无从下手啊01.mysql> alter table city add CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE;
02.ERROR 1005 (HY000): Can‘t create table ‘.\test\#sql-ed0_37.frm‘ (errno: 121)
@zhou :~$' title='03.zhouqian' ref='nofollow'>03.zhouqian
04.OS error code 121: Remote I/O error
05.MySQL error code 121: Duplicate key on write or update
06.
07.Can‘t create table ‘test.icity‘ (errno: 150)-----我这里也建立索引了。网上的说法是:字段类型和外键的索引
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)
@zhou :~$' title='zhouqian' ref='nofollow'>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的问题还是没有解决呢:
01.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;
02.Query OK, 0 rows affected (0.11 sec)
03.
04.mysql> show create table icity\G
05.*************************** 1. row ***************************
06. Table: icity
07.Create Table: CREATE TABLE `icity` (
08. `id` int(11) NOT NULL,
09. `city` varchar(20) DEFAULT NULL,
10. `country_id` smallint(5) unsigned NOT NULL,
11. PRIMARY KEY (`id`),
12. KEY `country_id` (`country_id`),
13. CONSTRAINT `icity_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
14.) ENGINE=InnoDB DEFAULT CHARSET=latin1
15.1 row in set (0.02 sec)
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。呵呵……
01.mysql> alter table city drop FOREIGN KEY `city_ibfk_1`;
02.Query OK, 0 rows affected (0.24 sec)
03.Records: 0 Duplicates: 0 Warnings: 0
04.
05.mysql> alter table city add FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE;Query OK, 0 rows affected (0.16 sec)
06.Records: 0 Duplicates: 0 Warnings: 0
07.
08.mysql> show create table city\G
09.*************************** 1. row ***************************
10. Table: city
11.Create Table: CREATE TABLE `city` (
12. `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
13. `city` varchar(50) NOT NULL,
14. `country_id` smallint(5) unsigned NOT NULL,
15. `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
16. PRIMARY KEY (`city_id`),
17. KEY `country_id` (`country_id`),
18. KEY `idx_fk_country_id` (`country_id`),
19. CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
20.) ENGINE=InnoDB DEFAULT CHARSET=utf8
21.1 row in set (0.00 sec)
大功告成
end
原文:http://my.oschina.net/bigfool007139/blog/511398
内容总结
以上是互联网集市为您收集整理的MySQL外键关联(创世纪新篇)全部内容,希望文章能够帮你解决MySQL外键关联(创世纪新篇)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。