The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-17. For the latest, interactive version please visit https://mariadb.com/kb/.

Broken foreign key constraint

We've recently migrated a Mariadb instance and a foreign key constraint got copied over incorrectly. It should be:

CONSTRAINT `cri_Sample_ibfk_1` FOREIGN KEY (`sampleType`) REFERENCES `cri_sample_type` (`type`),

but has come across as:

CONSTRAINT `cri_Sample_ibfk_1` FOREIGN KEY (`sampleType`) REFERENCES `cri_Sample_type` (`type`),

Note that the referenced table has gained a capital letter 'S'. This has broken things. Can anyone suggest a way to modify it back to what it should be please?

Answer Answered by Mr C in this comment.

OK, I think I've fixed it with:

SET FOREIGN_KEY_CHECKS=0;

alter table cri_Sample drop foreign key cri_Sample_ibfk_1;

ALTER TABLE cri_Sample ADD CONSTRAINT cri_Sample_ibfk_1 FOREIGN KEY (sampleType) REFERENCES cri_sample_type(type);

SET FOREIGN_KEY_CHECKS=1;

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.