外键限制问题

biotwang

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

为什么外键报错？

biotwang

尝试重演：

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. 
   
5. Database changed
   
6. mysql> show tables;
   
7. +--------------------+
   
8. | Tables_in_dbdao_po |
   
9. +--------------------+
   
10. | customers          |
    
11. | eng_test           |
    
12. | purchase_orders    |
    
13. | shipments          |
    
14. | test2              |
    
15. | test5              |
    
16. +--------------------+
    
17. 6 rows in set (0.00 sec)
    
18. 
    
19. mysql>
    
20. mysql>
    
21. mysql> create table degree_employee(id int not null auto_increment primary key, name varchar(10));
    
22. Query OK, 0 rows affected (0.05 sec)
    
23. 
    
24. mysql>
    
25. mysql> create table degree_salary(sa_id int auto_increment primary key, name varchar(10), foreign key(name) references degree_employee(name));
    
26. ERROR 1215 (HY000): Cannot add foreign key constraint

复制代码

biotwang

查看报错：

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. 
   
4. mysql> create table degree_salary(sa_id int auto_increment primary key, name varchar(10), foreign key(name) references degree_employee(name));
   
5. Query OK, 0 rows affected (0.04 sec)

复制代码

问题解决。

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

BruceFeng

由于我表已经建好，并且插入了数据，无法用建表语句来添加索引，所以采用了添加索引的语句

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. 
   
5. 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));
   
6. Query OK, 0 rows affected (0.06 sec)

复制代码

效果来看也解决了问题