MasAdam MasAdam - 6 months ago 10
SQL Question

How to Link Foreign Key with Different Name

I have 2 different tables: Profile and Transaction

Profile consists of: pID, firstName, lastName, phoneNumb

Transaction consists of: transID, sellerID, buyerID, itemID

My question is:

How to make sure that both sellerID and buyerID act as a foreign key in reference to profileID in Profile table?

My current code right now:

CREATE TABLE PROFILE
(
pID INT NOT NULL AUTO_INCREMENT ,
firstName VARCHAR(20) NOT NULL ,
lastName INT(20) NOT NULL ,
phoneNumb INT NOT NULL ,
PRIMARY KEY (pID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE TRANSACTION
(
tID INT NOT NULL AUTO_INCREMENT ,
sellerID INT ,
buyerID INT,
itemID INT,
PRIMARY KEY (tID),
FOREIGN KEY (sellerID, buyerID) REFERENCES PROFILE(pID),
FOREIGN KEY (itemID) REFERENCES ITEM (itemID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;


I tried this and it gave me this kind of error


1239 - Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match


Thanks.

Answer

I would go about it this way:

CREATE TABLE TRANSACTION 
( 
    tID INT NOT NULL AUTO_INCREMENT, 
    sellerID INT,
    buyerID INT,
    itemID INT, 
    PRIMARY KEY (tID),
    CONSTRAINT fk1 FOREIGN KEY (sellerID) REFERENCES PROFILE(pID)
    CONSTRAINT fk2 FOREIGN KEY (buyerID) REFERENCES PROFILE(pID)
    CONSTRAINT itemKey FOREIGN KEY (itemID) REFERENCES ITEM (itemID)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

This assumes that a table called ITEM exists which has a primary key called itemID. Your original problem mentioned only two tables. If ITEM does not exist, then either create it or remove the foreign key constraint from TRANSACTION.