Multiple foreign keys?

Question

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...

Answer 1

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.

Answer 2

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

Answer 3

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).

Answer 4

Indeed, error 150 refers to a foreign key constraints error. Can you provide the definition of the VENDOR table?

Is the PK of VENDOR an INT(6) or just an INT? From the above page:

• The size and sign of integer types must be the same.

Answer 5

Can you provide the definition of the VENDOR table

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

Thanks!

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

Answer 6

MyISAM is more for debugging and testing... InnoDB is better for production. Check mysql.com for caveats, as there are some benefits/risks with using either.

I'm actually having a similar problem - trying to declare 2 foreign keys in the same table (referencing two other tables)...

The following gives a 1064 error, and no useable line number (which also confuses me!):

CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, username TEXT, PRIMARY KEY (id) ) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE profiles ( id INT NOT NULL AUTO_INCREMENT, userID INT NOT NULL, profileName TEXT, PRIMARY KEY (id), FOREIGN KEY (userID) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE galleries ( id INT NOT NULL AUTO_INCREMENT, userID INT NOT NULL, galleryName TEXT, PRIMARY KEY (id), FOREIGN KEY (userID) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE; ) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE photos (
id INT NOT NULL AUTO_INCREMENT, userID INT NOT NULL, galleryID INT NOT NULL, photoName TEXT, PRIMARY KEY (id), FOREIGN KEY (userID) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (galleryID) REFERENCES galleries(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=INNODB DEFAULT CHARSET=utf8;

it's something to do with having more than 1 FK declaration... any ideas?? thanks!