how to reference multiple fks from different tables in MySQL

Question

i have been looking around and i cannot find the correct way to do this in sql the current way i have coded it just causes my third table to fail but the rest of the code worked great up until the third table, i just need the third table to include fks from both the previous tables

first table

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS Customer;
CREATE TABLE Customer
(
CustomerNumber int NOT NULL,
CustomerName varchar(255),
CustomerAddress varchar(255),
CustomerPhoneNumber varchar(255),
JoinDate varchar(255),
PetName varchar(255),
PayScheme varchar(255),
PremiumPayDate varchar(255),
PRIMARY KEY (CustomerNumber, PetName)
);

second table

DROP TABLE IF EXISTS Policies;    
CREATE TABLE Policies
(
PolicyID int NOT NULL,
PolicyNumber int NOT NULL,
PetType varchar(255),
CustomerNumber int NOT NULL, 
PetName varchar(255),
EffectiveDate varchar(255),
PRIMARY KEY (PolicyID),
    CONSTRAINT fk_CustomerNumber_PetName
    FOREIGN KEY (CustomerNumber, PetName)
    REFERENCES Customer(CustomerNumber, PetName)
);

third table

DROP TABLE IF EXISTS Claims;    
CREATE TABLE Claims
(
ClaimsID int NOT NULL,
AmountForReimbursement varchar(255),
PolicyID int NOT NULL,
PetName varchar(255),
    PRIMARY KEY (ClaimsID),
    CONSTRAINT fk_PolicyID_PetName
    FOREIGN KEY (PolicyID, PetName)
    REFERENCES Policies(PolicyID), Customer(PetName)        
);

Possible duplicate of [Foreign key refering to primary keys across multiple tables?](http://stackoverflow.com/questions/668921/foreign-key-refering-to-primary-keys-across-multiple-tables) — dstudeba, Jan 05 '17 at 20:47
The correct way to do *what*? A FK says that the values for some columns have to appear elsewhere as the values for some columns that are UNIQUE NOT NULL (which includes PK). You want a Claims policy-petname pair to also appear in Policies and a Policies customer-petname pair to appear in Customer. PS Why is petname in Claims when it is just the petname from the associated policy? — philipxy, Jan 05 '17 at 21:02
Possible duplicate of [MySQL foreign key causing table to drop](http://stackoverflow.com/questions/41492501/mysql-foreign-key-causing-table-to-drop) — philipxy, Jan 05 '17 at 21:04

Rahul · Accepted Answer · 2017-01-05T21:01:21.390

0

You should consider going through MySQL documentation. You have got the table creation wrong again for Claims. It should be like below

CREATE TABLE Claims
(
ClaimsID int NOT NULL,
AmountForReimbursement varchar(255),
PolicyID int NOT NULL,
PetName varchar(255),
CustomerNumber int NOT NULL,
    PRIMARY KEY (ClaimsID),
    CONSTRAINT fk_PolicyID_PetName
    FOREIGN KEY (PolicyID) REFERENCES Policies(PolicyID), 
    FOREIGN KEY (CustomerNumber, PetName) REFERENCES Customer(CustomerNumber, PetName)        
);

It's always recommended and you should actually refer both the PK column since you have a composite PK defined

edited Jan 05 '17 at 21:01

answered Jan 05 '17 at 20:48

Rahul

76,197
13
71
125

It will not work in MySQL. A foreign key can reference only the *left-most* column(s) of a key. In the OP's table, PetName is not left-most. – Bill Karwin Jan 05 '17 at 20:57
hmmmm this didn't work still receiving the same error as its saying it cannot create table? however i think the creation is now correct – luke griffiths Jan 05 '17 at 20:59
@lukegriffiths, see edit in answer if that helps. Thanks to Bill for pointing the mistake – Rahul Jan 05 '17 at 21:02
thanks @Rahul that seems to be working, so if its composite just refer to the whole pk – luke griffiths Jan 05 '17 at 21:07

how to reference multiple fks from different tables in MySQL

1 Answers1