MySQL 多个外键？

Multiple foreign keys?

提问by cmcculloh

I've got a table that is supposed to track days and costs for shipping product from one vendor to another. We (brilliantly :p) stored both the shipping vendors (FedEx, UPS) with the product handling vendors (Think... Dunder Mifflin) in a "VENDOR" table. So, I have three columns in my SHIPPING_DETAILS table that all reference VENDOR.no. For some reason MySQL isn't letting me define all three as foreign keys. Any ideas?

我有一张表，用于跟踪将产品从一个供应商运送到另一个供应商的天数和成本。我们（出色地：p）将运输供应商（FedEx、UPS）和产品处理供应商（Think... Dunder Mifflin）存储在“供应商”表中。所以，我的 SHIPPING_DETAILS 表中有三列都引用了 VENDOR.no。出于某种原因，MySQL 不允许我将所有三个都定义为外键。有任何想法吗？

CREATE TABLE SHIPPING_GRID(  
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique ID for each row',  
    shipping_vendor_no INT(6) NOT NULL COMMENT 'Foreign key to VENDOR.no for the shipping vendor (vendors_type must be 3)',  
    start_vendor_no INT(6) NOT NULL COMMENT 'Foreign key to VENDOR.no for the vendor being shipped from',  
    end_vendor_no INT(6) NOT NULL COMMENT 'Foreign key to the VENDOR.no for the vendor being shipped to',  
    shipment_duration INT(1) DEFAULT 1 COMMENT 'Duration in whole days shipment will take',  
    price FLOAT(5,5) NOT NULL COMMENT 'Price in US dollars per shipment lbs (down to 5 decimal places)',  
    is_flat_rate TINYINT(1) DEFAULT 0 COMMENT '1 if is flat rate regardless of weight, 0 if price is by lbs',  
    INDEX (shipping_vendor_no),  
    INDEX (start_vendor_no),  
    INDEX (end_vendor_no),  
    FOREIGN KEY (shipping_vendor_no) REFERENCES VENDOR (no),  
    FOREIGN KEY (start_vendor_no) REFERENCES VENDOR (no),  
    FOREIGN KEY (end_vendor_no) REFERENCES VENDOR (no)  
) TYPE = INNODB;

Edited to remove double primary key definition...

编辑删除双主键定义...

---

---

Yeah, unfortunately that didn't fix it though. Now I'm getting:

是的，不幸的是，这并没有解决它。现在我得到：

Can't create table './REMOVED MY DB NAME/SHIPPING_GRID.frm' (errno: 150)

无法创建表 './ REMOVED MY DB NAME/SHIPPING_GRID.frm' (errno: 150)

Doing a phpinfo() tells me this for mysql:

为 mysql 做一个 phpinfo() 告诉我这个：

Client API version 5.0.45

客户端 API 版本 5.0.45

Yes, the VENDOR.no is type int(6).

是的，VENDOR.no 是 int(6) 类型。

采纳答案by Christian Lescuyer

You defined the primary key twice. Try:

您定义了两次主键。尝试：

CREATE TABLE SHIPPING_GRID(  
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique ID for each row',  
    shipping_vendor_no INT(6) NOT NULL COMMENT 'Foreign key to VENDOR.no for the shipping vendor (vendors_type must be 3)',  
    start_vendor_no INT(6) NOT NULL COMMENT 'Foreign key to VENDOR.no for the vendor being shipped from',  
    end_vendor_no INT(6) NOT NULL COMMENT 'Foreign key to the VENDOR.no for the vendor being shipped to',  
    shipment_duration INT(1) DEFAULT 1 COMMENT 'Duration in whole days shipment will take',  
    price FLOAT(5,5) NOT NULL COMMENT 'Price in US dollars per shipment lbs (down to 5 decimal places)',  
    is_flat_rate TINYINT(1) DEFAULT 0 COMMENT '1 if is flat rate regardless of weight, 0 if price is by lbs',  
    INDEX (shipping_vendor_no),  
    INDEX (start_vendor_no),  
    INDEX (end_vendor_no),  
    FOREIGN KEY (shipping_vendor_no) REFERENCES VENDOR (no),  
    FOREIGN KEY (start_vendor_no) REFERENCES VENDOR (no),  
    FOREIGN KEY (end_vendor_no) REFERENCES VENDOR (no)  
) TYPE = INNODB;

The VENDOR primary key must be INT(6), and both tables must be of type InnoDB.

VENDOR 主键必须是 INT(6)，并且两个表都必须是 InnoDB 类型。

回答by Mario Marinato

I ran the code here, and the error message showed (and it is right!) that you are setting idfield twice as primary key.

我在这里运行了代码，错误消息显示（这是正确的！）您将id字段设置为主键两次。

回答by cmcculloh

Can you provide the definition of the VENDOR table

你能提供VENDOR表的定义吗

I figured it out. The VENDOR table was MyISAM... (edited your answer to tell me to make them both INNODB ;) )

我想到了。VENDOR 表是 MyISAM ......（编辑你的答案告诉我让它们都是 INNODB ;））

(any reason notto just switch the VENDOR type over to INNODB?)

（有什么理由不只是将 VENDOR 类型切换到 INNODB？）