/*
	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';