多个外键？ [英] Multiple foreign keys?

问题描述

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

$ pre $ $ $ codeREATE TABLE SHIPPING_GRID
INT INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT' ，
shipping_vendor_no INT（6）NOT NULL COMMENT'对于运输供应商的VENDOR.no的外键（vendors_type必须为3）'，
start_vendor_no INT（6）NOT NULL COMMENT'VENDOR的外键。 （$）b $ end_vendor_no INT（6）NOT NULL COMMENT'供应商发货给VENDOR.no的外键'，
shipment_duration INT（1）DEFAULT 1 COMMENT' （5,5）NOT NULL COMMENT'美元每件货物的价格lbs（小数点后5位）'，
is_flat_rate TINYINT（1）DEFAULT 0评论'1如果统一费率不考虑重量，0如果价格是由磅'，
INDEX（shipping_vendor_no），
INDEX（start_vendor_no），
INDEX（end_vendor_no），
FOREIGN KEY（ （no），
FOREIGN KEY（start_vendor_no）REFERENCES VENDOR（no），
FOREIGN KEY（end_vendor_no）REFERENCES VENDOR（no）
）TYPE = INNODB;

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

---

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

lockquote

无法创建表
./ 重新获得MY DB NAME /SHIPPING_GRID.frm'
（errno：150）

执行phpinfo（） / b>

客户端API版本5.0.45

是的，VENDOR.no是类型int（6）。

解决方案

您定义了两次主键。试试：
$ b $ pre $ $ codeREATE TABLE SHIPPING_GRID（
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT'每行的唯一ID'，
shipping_vendor_no INT（6）NOT NULL COMMENT'VENDOR.no的外键（shipping_type必须为3）'，
start_vendor_no INT（6）NOT NULL COMMENT'VENDOR.no的外键对于从'，
发货的供应商，end_vendor_no INT（6）NOT NULL COMMENT'供应商发货到'VENDOR.no的外键，
shipment_duration INT（1）DEFAULT 1 COMMENT'持续时间（5,5）NOT NULL COMMENT'美元每件货物的价格lbs（小数点后5位）'，
is_flat_rate TINYINT（1）DEFAULT 0评论'1，如果是统一费率不考虑重量，0如果价格是由磅'，
INDEX（shipping_vendor_no），
INDEX（start_vendor_no），
INDEX（end_vendor_no），
外国关键（shippi （no），
FOREIGN KEY（start_vendor_no）REFERENCES VENDOR（no），
FOREIGN KEY（end_vendor_no）REFERENCES VENDOR（no）
）TYPE = INNODB;

VENDOR主键必须是INT（6），而且这两个表必须是InnoDB类型。 / p>

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?

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)

Doing a phpinfo() tells me this for mysql:

Client API version 5.0.45

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

解决方案

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.

这篇关于多个外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！