Resolved: how to solve this error
Operation failed: There was an error while applying the SQL script to the database.
Executing:
ALTER TABLE `sales`.`sales`
ADD INDEX `gui_idx` (`customer_id` ASC) VISIBLE;
;
ALTER TABLE `sales`.`sales`
ADD CONSTRAINT `gui`
FOREIGN KEY (`customer_id`)
REFERENCES `sales`.`customers` (`customer_id`)
ON DELETE CASCADE
ON UPDATE NO ACTION;
ERROR 1822: Failed to add the foreign key constraint. Missing index for constraint 'gui' in the referenced table 'customers'
SQL Statement:
ALTER TABLE `sales`.`sales`
ADD CONSTRAINT `gui`
FOREIGN KEY (`customer_id`)
REFERENCES `sales`.`customers` (`customer_id`)
ON DELETE CASCADE
ON UPDATE NO ACTION
Hi Magesh!
Thanks for reaching out.
The error you are encountering indicates that the foreign key constraint you're trying to add cannot be established because the referenced table `customers` does not have an index on the `customer_id` column, which is necessary for a foreign key constraint.
To fix this issue, you should first ensure that the `customer_id` column in the `customers` table is indexed. If it's not, you need to create an index on that column with the following SQL statement before you can add the foreign key constraint:
ALTER TABLE `sales`.`customers` ADD INDEX `customer_id_index`(`customer_id` ASC);
After creating the index on the `customers` table, you can then try adding the foreign key constraint to the `sales` table again. This should resolve the error you're seeing.
Hope this helps.
Best,
Tsvetelin