[SQL] Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint...

I have two tables in MySQL, 'orders' and 'rota' and I am trying to connect the 'created_date' column from 'orders' with 'date' from table 'rota' using a foreign key.

CREATE TABLE `orders` (
`row_id` int NOT NULL ,
`order_id` varchar(10) NOT NULL ,
`created_date` datetime NOT NULL ,
`item_id` varchar(10) NOT NULL ,
`quantity` int NOT NULL ,
`customer_id` int NOT NULL ,
`delivery` boolean NOT NULL ,
`address_id` int NOT NULL ,
PRIMARY KEY (
`row_id`
)
);

CREATE TABLE `rota` (
`row_id` int NOT NULL ,
`rota_id` varchar(20) NOT NULL ,
`date` datetime NOT NULL ,
`shift_id` varchar(20) NOT NULL ,
`staff_id` varchar(20) NOT NULL ,
PRIMARY KEY (
`row_id`
)
);

ALTER TABLE `orders` ADD CONSTRAINT `fk_orders_created_date`
FOREIGN KEY(`created_date`)
REFERENCES `rota` (`date`);


Here is the error:

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint 'fk_orders_created_date' in the referenced table 'rota' 0.000 sec
​

Continue reading...