MySQL对两个不同的表列使用相同的外键 [英] MySQL Using Same Foreign key for two different table columns

问题描述

我发现此线程与我的查询类似 如何链接具有不同名称的外键 但不幸的是，答案没有解决我的问题.在我的表格示例中，它不会创建任何主键.仅限所有外键.

I found this thread similar to my queries How to Link Foreign Key with Different Name But unfortunately, with the answer, my problem didn't get solved. As on my table example, it doesn't creates any primary key. All foreign keys only.

这是代码:

CREATE TABLE ref_data(
user_id INT(11) NOT NULL,
ref_id INT(11) NOT NULL,
ref_name VARCHAR(30) NOT NULL,
ref_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT FK_user_id FOREIGN KEY(user_id) REFERENCES client (user_id),
CONSTRAINT FK_ref_id FOREIGN KEY(ref_id) REFERENCES client (user_id),
CONSTRAINT FK_ref_name FOREIGN KEY(ref_name) REFERENCES client (firstname)
);

它给了我:

errno:150外键约束的格式不正确"

errno: 150 "Foreign key constraint is incorrectly formed"

在这里，我使用user_id两次，其中第一个用作"user_id"，第二个用作"ref_id".还将名字用作"ref_name".

Here I am using user_id two times with 1st as "user_id" 2nd as "ref_id". Also using firstname as "ref_name".

客户表:

   CREATE TABLE client (
   `user_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(30) NOT NULL UNIQUE KEY,
  `email` VARCHAR(50) NOT NULL UNIQUE KEY,
  `firstname` VARCHAR(30) NOT NULL,
   `lastname` VARCHAR(30) NOT NULL,
  `password` CHAR(128) NOT NULL
  );

推荐答案

好，当尝试创建ref_data表时，在外键错误之后，我看到了这一点:

Ok, when trying to create the ref_data table, after the foreign key error, I see this:

最新外键错误

LATEST FOREIGN KEY ERROR

------------------------ 2017-07-13 01:07:00 37ec表ref_data的外键约束错误:

------------------------ 2017-07-13 01:07:00 37ec Error in foreign key constraint of table ref_data:

FOREIGN KEY(ref_name)参考客户端(名字)):

FOREIGN KEY(ref_name) REFERENCES client (firstname) ):

在 被引用的表，其中被引用的列显示为第一列 列或表和引用表中的列类型不 匹配约束.请注意，内部存储类型为ENUM和 SET在使用> = InnoDB-4.1.12创建的表中更改，并且此类列 旧表中的列不能被新表中的此类列引用.看 http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html 用于正确的外键定义.创建表'test.ref_data' 外键约束失败. 引用表，其中引用列出现在第一列 "FOREIGN KEY(ref_name)REFERENCES client(firstname))"附近的列.

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. See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition. Create table 'test.ref_data' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' FOREIGN KEY(ref_name) REFERENCES client (firstname) )'.

该错误的基本含义是:(以粗体显示)

What that error is basically saying: (in the bold text)

客户"表的名字"上没有索引(FOREIGN KEY的REFERENCES子句后的部分

但这是一个简单的解决方法.在 client 表上运行此SQL:

But it's a simple fix. Run this SQL on the client table:

ALTER TABLE `client` ADD INDEX(`firstname`);

...，然后再次运行ref_data表SQL.

... and then run the ref_data table SQL again.

这篇关于MySQL对两个不同的表列使用相同的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！