我有一张桌子，该桌子应该可以追踪从一家供应商向另一家供应商运送产品的天数和成本。我们(出色地：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;

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

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

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

做一个phpinfo()会告诉我mysql：

Client API version    5.0.45

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

解决方案

回答

我们定义了两次主键。尝试：

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;

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

回答

我在这里运行代码，错误消息显示(正确！)，我们将id字段设置为两次主键。

回答

Can you provide the definition of the
  VENDOR table

我想到了。 VENDOR表是MyISAM ...(编辑回答，告诉我将它们都设置为INNODB;))

(有什么理由不只是将VENDOR类型切换到INNODB吗？)