无法在MySQL 5.7中添加外键(引用表中的缺少约束) [英] Unable to add foreign key in MySQL 5.7 (Missing constraint in the referenced table)
问题描述
我正在尝试如下运行外键添加查询，并将外键检查设置为0.两个表中的两列完全相同.而且，两者都是主键.这里没有任何解决方案有助于解决此问题.我在本地主机上.
I am trying to run a foreign key add query as below, with foreign key checks set to 0. Both the columns in the two tables are exactly the same. Also, both are primary keys. None of the solutions here helped in solving this problem. I'm on localhost.
mysql> alter table deliveryaddress
-> add foreign key(oid) references productorder(oid)
-> on delete cascade on update restrict;
ERROR 1822 (HY000): Failed to add the foreign key constaint. Missing index for c
onstraint '' in the referenced table 'productorder'
mysql> desc productorder;
+----------------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+-------------------+-------+
| primenumber | varchar(15) | NO | PRI | NULL | |
| oid | varchar(10) | NO | PRI | NULL | |
| orderdatetime | timestamp | NO | | CURRENT_TIMESTAMP | |
| addressname | varchar(30) | NO | | NULL | |
| deliverycharge | decimal(8,2) | YES | | 20.00 | |
+----------------+--------------+------+-----+-------------------+-------+
5 rows in set (0.02 sec)
mysql> desc deliveryaddress;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid | varchar(10) | NO | PRI | NULL | |
| pincode | varchar(8) | NO | | NULL | |
| area | varchar(60) | NO | | NULL | |
| city | varchar(60) | NO | | NULL | |
| state | varchar(60) | NO | | NULL | |
| landmark | varchar(60) | YES | | NULL | |
| phone | varchar(15) | NO | | NULL | |
| locality | varchar(60) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql>
推荐答案
它看起来像 productorder.oid 是多列主键的一部分，而不是主键中最左边的列.(将来，请包括 SHOW CREATE TABLE< tablename> 的结果，因为与DESCRIBE相比，它更了解多列键.)
It looks like productorder.oid is part of a multi-column primary key, and it's not the leftmost column in the primary key. (In the future, please include the result of SHOW CREATE TABLE <tablename> because it is more clear than DESCRIBE about things like multi-column keys.)
声明外键时，必须引用主键的最左列.
When you declare a foreign key, you must reference the leftmost column of the primary key.
当您引用多列主键时，外键必须具有相同顺序的相同列数.
When you reference a multi-column primary key, the foreign key must have the same number of columns in the same order.
错误(列数不足，并且引用了主键的第二列):
CREATE TABLE parent (x INT, y INT, PRIMARY KEY (x, y));
CREATE TABLE child (y INT, FOREIGN KEY (y) REFERENCES parent(y));
错误(每个外键分别引用复合主键的一部分):
CREATE TABLE parent (x INT, y INT, PRIMARY KEY (x, y));
CREATE TABLE child (x INT, y INT,
FOREIGN KEY (x) REFERENCES parent(x),
FOREIGN KEY (y) REFERENCES parent(y)
);
右(相同列):
CREATE TABLE parent (x INT, y INT, PRIMARY KEY (x, y));
CREATE TABLE child (x INT, y INT, FOREIGN KEY (x, y) REFERENCES parent(x, y));
发表您的评论
Re your comment:
我现在在想，您的真正问题是您的恋爱关系逆转了.您正在尝试在引用 productorder 的 deliveryaddress 中声明外键，但是我希望引用会朝另一个方向发展.
I'm now thinking that your real problem is that you have the relationship reversed. You are trying to declare a foreign key in deliveryaddress referencing productorder, but I would expect the reference to go the other direction.
ALTER TABLE productorder ADD FOREIGN KEY (oid) REFERENCES deliveryaddress (oid);
那么您就不会出错，因为 deliveryaddress 的主键只是一列.
Then you have no error, because the primary key of deliveryaddress is just one column.
我相信这种关系在典型的电子商务应用程序中更有意义.有很多订单可能引用相同的地址.相反的关系可能不是您想要的，因为对于许多地址引用单个产品订单来说，这毫无意义.
I believe this relationship makes more sense in a typical e-commerce application. There are many orders that might reference the same address. The opposite relationship is probably not what you want, because it makes no sense for many addresses to reference a single product order.
这篇关于无法在MySQL 5.7中添加外键(引用表中的缺少约束)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
