Can't create a table in mySQL-CodePudding

I am trying a table where the primary keys are foreing keys of a table where the primary keys are foreing keys, but it doesn't create the table and I don't know why its not creating.

Table that I want to create:

CREATE TABLE proj__exames(
    floor NUMERIC(2), 
    room NUMERIC(3), 
    name VARCHAR(40), 
    PRIMARY KEY (floor, room, name),
    FOREIGN KEY (floor) REFERENCES proj__rooms (floor) ON DELETE CASCADE,
    FOREIGN KEY (room) REFERENCES proj__rooms (room) ON DELETE CASCADE,
    FOREIGN KEY (name) REFERENCES proj__rooms (name) ON DELETE CASCADE
);

Table where I am getting the foreign key from (this was created without any problems)

CREATE TABLE proj__rooms(
    floor NUMERIC(2),
    room NUMERIC(3),
    name VARCHAR(40),
    equipment_type VARCHAR(40),
    max_exam_number NUMERIC(2) NOT NULL,
-- ========
    CONSTRAINT pk_rooms
        PRIMARY KEY (name, floor, room),
-- ========
    CONSTRAINT fk_salas_nome   
        FOREIGN KEY (name)
            REFERENCES clinic (name) ON DELETE CASCADE,
-- ========
    CONSTRAINT ck_rooms_max_exam_number
        CHECK (max_exam_number >= 0) 
);

CodePudding user response：

The error turned out to be missing indexes on the referenced columns. Basically you'd have to restructure the proj__rooms DDL like this:

CREATE TABLE proj__rooms(
    floor NUMERIC(2),
    room NUMERIC(3),
    name VARCHAR(40),
    equipment_type VARCHAR(40),
    max_exam_number NUMERIC(2) NOT NULL,   
    -- ========
    CONSTRAINT pk_rooms
        PRIMARY KEY (name, floor, room),
    KEY(floor),
    KEY(room),
    KEY(name),
    -- ========
    CONSTRAINT fk_salas_nome   
        FOREIGN KEY (name)
            REFERENCES clinic (name) ON DELETE CASCADE,
    -- ========
    CONSTRAINT ck_rooms_max_exam_number
        CHECK (max_exam_number >= 0) 
);

I also suggest you to add the MySQL console error in futher questions, it could make it easier to understand the problem you're facing