外键限制问题
From X-Ora上海-Bruce 11:53:29 AM：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);第一句成功了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为什么外键报错？ 尝试重演：mysql> use dbdao_po;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+--------------------+
| Tables_in_dbdao_po |
+--------------------+
| customers |
| eng_test |
| purchase_orders |
| shipments |
| test2 |
| test5 |
+--------------------+
6 rows in set (0.00 sec)
mysql>
mysql>
mysql> create table degree_employee(id int not null auto_increment primary key, name varchar(10));
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> create table degree_salary(sa_id int auto_increment primary key, name varchar(10), foreign key(name) references degree_employee(name));
ERROR 1215 (HY000): Cannot add foreign key constraint 本帖最后由 biotwang 于 2016-9-9 13:18 编辑
查看报错：mysql> show engine innodb status;查看到报错原因分析：------------------------
LATEST FOREIGN KEY ERROR
------------------------
2016-09-09 12:06:19 0x7f7788b11700 Error in foreign key constraint of table dbdao_po/degree_salary:
foreign key(name) references degree_employee(name)):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
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/5.7/en/innodb-foreign-key-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.
因此重新清理测试表并修改语句，测试如下：mysql> create table degree_employee(id int not null auto_increment primary key, name varchar(10), index (name));
Query OK, 0 rows affected (0.06 sec)
mysql> create table degree_salary(sa_id int auto_increment primary key, name varchar(10), foreign key(name) references degree_employee(name));
Query OK, 0 rows affected (0.04 sec)问题解决。
总结：
说明MySQL外键被参考列必须为索引列。
本帖最后由 BruceFeng 于 2016-9-9 13:22 编辑
由于我表已经建好，并且插入了数据，无法用建表语句来添加索引，所以采用了添加索引的语句mysql> CREATE INDEX employee_name on degree_employee (name);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
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));
Query OK, 0 rows affected (0.06 sec)效果来看也解决了问题
页:
[1]