mysqlforeign-keyscomposite-primary-key

Can a Foreign Key be part of a Composite Primary Key for another table?

I have two(of many) tables in a music database:

Concerts: ArtistID, ConcertID, ConcetName, VenueID  
ConcertDetails: ConcertDate, ConcertID, Cost

The ConcertDetails tables as you see, uses ConcertID which is also in the Concerts table.

I combined ConcertDate & ConcertID to make a Composite Primary Key for ConcertDetails.

However since this relates to ConcertID from Concerts table, it also needs to be a Foreign Key. Is this ok to do?

Solution

  • Yes, of course. It's common for a subset of a primary key to be a foreign key. Any many-to-many table does this for instance. In your case:

    CREATE TABLE ConcertDetails (
  ConcertDate DATE NOT NULL,
  ConcertID INT NOT NULL,
  PRIMARY KEY (ConcertDate, ConcertID),
  FOREIGN KEY (ConcertID) REFERENCES Concerts(ConcertID)
);