无法在 MySQL 5.7 中添加外键(引用表中缺少约束)
我正在尝试运行如下的外键添加查询，外键检查设置为 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:
我现在认为你真正的问题是你的关系颠倒了.您正试图在 deliveryaddress 中声明一个引用 productorder 的外键，但我希望该引用指向另一个方向.
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.
相关文章