Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

205

积分

19

好友

29

主题

[MySQL Base] 外键限制问题

发表于 2016-9-9 13:02:17 | 查看: 3748| 回复: 3
From X-Ora上海-Bruce   11:53:29 AM:
  1. CREATE TABLE degree_employee( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10) NOT NULL, age INT(10),timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
复制代码
第一句成功了
  1. CREATE TABLE degree_salary (sa_id INT AUTO_INCREMENT PRIMARY KEY,name varchar(10) ,salary DECIMAL(5,2) DEFAULT 0.0,FOREIGN KEY (name) REFERENCES degree_employee (name));
复制代码
第二句报ERROR 1215 (HY000): Cannot add foreign key constraint

为什么外键报错?
发表于 2016-9-9 13:04:48
尝试重演:
  1. mysql> use dbdao_po;
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A

  4. Database changed
  5. mysql> show tables;
  6. +--------------------+
  7. | Tables_in_dbdao_po |
  8. +--------------------+
  9. | customers          |
  10. | eng_test           |
  11. | purchase_orders    |
  12. | shipments          |
  13. | test2              |
  14. | test5              |
  15. +--------------------+
  16. 6 rows in set (0.00 sec)

  17. mysql>
  18. mysql>
  19. mysql> create table degree_employee(id int not null auto_increment primary key, name varchar(10));
  20. Query OK, 0 rows affected (0.05 sec)

  21. mysql>
  22. mysql> create table degree_salary(sa_id int auto_increment primary key, name varchar(10), foreign key(name) references degree_employee(name));
  23. ERROR 1215 (HY000): Cannot add foreign key constraint
复制代码

回复 显示全部楼层 道具 举报

发表于 2016-9-9 13:10:20
本帖最后由 biotwang 于 2016-9-9 13:18 编辑

查看报错:
  1. mysql> show engine innodb status;
复制代码
查看到报错原因分析:
  1. ------------------------
  2. LATEST FOREIGN KEY ERROR
  3. ------------------------
  4. 2016-09-09 12:06:19 0x7f7788b11700 Error in foreign key constraint of table dbdao_po/degree_salary:
  5. foreign key(name) references degree_employee(name)):
  6. Cannot find an index in the referenced table where the
  7. referenced columns appear as the first columns, or column types
  8. in the table and the referenced table do not match for constraint.
  9. Note that the internal storage type of ENUM and SET changed in
  10. tables created with >= InnoDB-4.1.12, and such columns in old tables
  11. cannot be referenced by such columns in new tables.
  12. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.
复制代码
感觉分析不是很合理,但是可能和索引有关,查文档:
http://dev.mysql.com/doc/refman/ ... ey-constraints.html

发现以下说明:
InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.


因此重新清理测试表并修改语句,测试如下:
  1. mysql> create table degree_employee(id int not null auto_increment primary key, name varchar(10), index (name));
  2. Query OK, 0 rows affected (0.06 sec)

  3. mysql> create table degree_salary(sa_id int auto_increment primary key, name varchar(10), foreign key(name) references degree_employee(name));
  4. Query OK, 0 rows affected (0.04 sec)
复制代码
问题解决。

总结:
说明MySQL外键被参考列必须为索引列。


回复 显示全部楼层 道具 举报

发表于 2016-9-9 13:21:42
本帖最后由 BruceFeng 于 2016-9-9 13:22 编辑

由于我表已经建好,并且插入了数据,无法用建表语句来添加索引,所以采用了添加索引的语句
  1. mysql> CREATE INDEX employee_name on degree_employee (name);
  2. Query OK, 0 rows affected (0.07 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0

  4. mysql> CREATE TABLE degree_salary (SA_id INT AUTO_INCREMENT PRIMARY KEY,name varchar(10) NOT NULL ,salary DECIMAL(5,2) DEFAULT 0.0,FOREIGN KEY (name) REFERENCES degree_employee (name));
  5. Query OK, 0 rows affected (0.06 sec)
复制代码
效果来看也解决了问题

回复 显示全部楼层 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-3-29 05:06 , Processed in 0.062153 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569