How can i solve foreign key no index parent?

Question

i only started learning SQL myself for the past 2 days and now i encounter a problem with linking column on different table using foreign key constraint. Below is my code.

    CREATE TABLE analytics (
    id INT NOT NULL,
    status BOOLEAN,
    server_id INT,  /* link with server info*/     
    source_id INT,      /*link with input source*/
    ext VARCHAR(5),
    startframe_id_x INT,
    endframe_id_x INT,
    mask VARCHAR(20),
    label VARCHAR(20),
    countline INT,
    det_deviceid INT,
    processing_period TIME,
    PRIMARY KEY(id),
    FOREIGN KEY (server_id) REFERENCES server_info(id),
    FOREIGN KEY (source_id) REFERENCES input_source(id)
);


CREATE TABLE statistics (
    id INT NOT NULL,
    source_id INT,  /*link with input source*/
    analytic_id INT, /*link with analytic*/
    time_recorded TIMESTAMP,
    duration TIME,   /*link with analytics processing period*/
    counter INT,
    PRIMARY KEY (id),
    FOREIGN KEY (source_id) REFERENCES input_source(id),
    FOREIGN KEY (analytic_id) REFERENCES analytics(id),
    FOREIGN KEY (duration) REFERENCES analytics(processing_period)
);

the problem occurs on this line

   FOREIGN KEY (duration) REFERENCES analytics(processing_period)

which i am not sure and used up countless of hours searching and figuring out solutions but still can't fix it.

It gave out error like this "ER_FK_INDEX_PARENT: Failed to add the foreign key constraint. Missing index for constraint 'statistics_ibfk_3' in the referenced table 'analytics'"

anyone could give out any idea why this problem occurs? im using Popsql to edit my code and use the mysql database.

Would appreciate some explanation or solution.

Answer 1

Your second table should look like this:

CREATE TABLE statistics (
    id INT NOT NULL,
    source_id INT,  /*link with input source*/
    analytic_id INT, /*link with analytic*/
    time_recorded TIMESTAMP,
    counter INT,
    PRIMARY KEY (id),
    FOREIGN KEY (source_id) REFERENCES input_source(id),
    FOREIGN KEY (analytic_id) REFERENCES analytics(id)
);

Note that duration has been removed. If you want the processing period, then use JOIN to match to the analytics table.