tables - two foreign keys referencing same column

Multiple foreign keys? (3)

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

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

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

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

How to add 2 foreign keys in a table in a SQL Server?

The foreign key syntax is:

 FOREIGN KEY (addid) REFERENCES Table1_Addr(addid),
 FOREIGN KEY (id) REFERENCES Table1(id)

For Table Booking. Here's what i have noticed NRIC should be on the table before setting it as foreign key:

CREATE TABLE BOOKING 
(BookingID char(4) primary key
, dateBooked datetime not null
, creditCard char(16) null
, expiryDate datetime not null
, CVC char (3) not null
, confirmationID char(4) not null
, ticketType varchar(20) 
, NRIC char(9)    
,FOREIGN KEY (ticketType) REFERENCES ticketType(ticketType)
,FOREIGN KEY (NRIC) REFERENCES Patron(NRIC))

For BookingSeat you don't have a primary key on this table? I noticed that you defined seatNo twice.

CREATE TABLE BookingSeat 
(seatNo char(2) not null
, rowNo char(2) not null
, date datetime not null
, startTime time not null
, rowNo char(2) not null
, FOREIGN KEY (date) REFERENCES hallSchedule(date)
, FOREIGN KEY (startTime) REFERENCES hallSchedule(startTime)
, FOREIGN KEY (rowNo) REFERENCES Seat(rowNo)
, FOREIGN KEY (seatNo) REFERENCES Seat(seatNo))

Please see these links for references: Can a table have two foreign keys? Multiple foreign keys?