Foreign Key of Composite Primary Key and Another Attribute

Question

The tables of my concern:

CREATE TABLE Branch(
    branchID        varchar(5) PRIMARY KEY,
    branchName      varchar(100),
    city            varchar(100),
    phone           varchar(15)
)Engine=InnoDB;

CREATE TABLE Theatre(
    theatreID       varchar(5),
    branchID        varchar(5),
    theatreType     varchar(50),
    rowCapacity     INT,
    seatCapacity    INT,

    FOREIGN KEY(branchID) REFERENCES Branch(branchID),
    PRIMARY KEY(theatreID,branchID)
)Engine=InnoDB;

CREATE TABLE Day(
    dayID           varchar(4) PRIMARY KEY,
    dayName         varchar(20),
    dayPrice        int
)Engine=InnoDB;

CREATE TABLE Price(
    theatreID       varchar(5),
    branchID        varchar(5),
    theatreType     varchar(50),
    moviePrice      INT,
    dayID           varchar(4),
    FOREIGN KEY(theatreID,branchID,theatreType) 
        REFERENCES Theatre(theatreID,branchID,theatreType),
    FOREIGN key(dayID) REFERENCES Day(dayID),
    PRIMARY KEY(branchID,dayID,theatreType)
)Engine=InnoDB;

I learnt that foreign key, which references to a table with composite primary key, needs to have all the attributes of said composite primary key.

This means to link table Theatre and Price, I have to add

FOREIGN KEY(theatreID,branchID) 
        REFERENCES Theatre(theatreID,branchID)

The code still works.

But, if I want to add another foreign key, 'theatreType'. It returns error 1005.

    FOREIGN KEY(theatreID,branchID,theatreType) 
        REFERENCES Theatre(theatreID,branchID,theatreType)

Where did I do wrong? Thanks in advance.

Answer 1

The foreign key and the referenced key need to have the same structure. If you update the Theatre table in the following way, it will work

CREATE TABLE Theatre(
    theatreID       varchar(5),
    branchID        varchar(5),
    theatreType     varchar(50),
    rowCapacity     INT,
    seatCapacity    INT,

    FOREIGN KEY(branchID) REFERENCES Branch(branchID),
    PRIMARY KEY(theatreID,branchID,theatreType)
)Engine=InnoDB;