/* for use if diagnoses table renamed to _diagnoses before copying new table across better alternative in update-diagnosis-table.sql */ USE information_schema; SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = '_diagnoses' AND REFERENCED_COLUMN_NAME = 'id'; ALTER TABLE `diagnosis_lab_test` DROP FOREIGN KEY `diagnosis_lab_test_ibfk_1`; ALTER TABLE `diagnosis_lab_test` ADD CONSTRAINT `diagnosis_lab_test_ibfk_1` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`); ALTER TABLE `context_warning_diagnosis` DROP FOREIGN KEY `context_warning_diagnosis_ibfk_1`; ALTER TABLE `context_warning_diagnosis` ADD CONSTRAINT `context_warning_diagnosis_ibfk_1` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`); ALTER TABLE `request_authorisation_diagnosis` DROP FOREIGN KEY `request_authorisation_diagnosis_ibfk_2`; ALTER TABLE `request_authorisation_diagnosis` ADD CONSTRAINT `request_authorisation_diagnosis_ibfk_2` FOREIGN KEY (`auth_diagnosis_id`) REFERENCES `diagnoses` (`id`); ALTER TABLE `request_authorisation_diagnosis` DROP FOREIGN KEY `request_authorisation_diagnosis_ibfk_3`; ALTER TABLE `request_authorisation_diagnosis` ADD CONSTRAINT `request_authorisation_diagnosis_ibfk_3` FOREIGN KEY (`report_diagnosis_id`) REFERENCES `diagnoses` (`id`); ALTER TABLE `request_diagnosis_history` DROP FOREIGN KEY `request_diagnosis_history_ibfk_3`; ALTER TABLE `request_diagnosis_history` ADD CONSTRAINT `request_diagnosis_history_ibfk_3` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`); ALTER TABLE `request_draft_report` DROP FOREIGN KEY `request_draft_report_ibfk_2`; ALTER TABLE `request_draft_report` ADD CONSTRAINT `request_draft_report_ibfk_2` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`); ALTER TABLE `request_draft_report` DROP FOREIGN KEY `request_draft_report_ibfk_4`; ALTER TABLE `request_draft_report` ADD CONSTRAINT `request_draft_report_ibfk_4` FOREIGN KEY (`secondary_diagnosis_id`) REFERENCES `diagnoses` (`id`); ALTER TABLE `request_secondary_diagnosis` DROP FOREIGN KEY `request_secondary_diagnosis_ibfk_2`; ALTER TABLE `request_secondary_diagnosis` ADD CONSTRAINT `request_secondary_diagnosis_ibfk_2` FOREIGN KEY (`secondary_diagnosis_id`) REFERENCES `diagnoses` (`id`); USE information_schema; SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'diagnoses' AND REFERENCED_COLUMN_NAME = 'id';