/*
  MySQL script to revert referral details list changes
  Moulya 15 March 2019

 */

SET SQL_SAFE_UPDATES = 0;
delete from REFERRAL_DETAILS where item_index!=0;
alter table carespell add REFERRAL_ID int null;

update carespell cs set cs.REFERRAL_ID =
    (select REFERRAL_ID from REFERRAL_DETAILS rd where rd.carespell_id = cs.carespell_id);

alter table REFERRAL_DETAILS
  drop foreign key FK_9q0ap458nr66uxy7pkd7ag3sv;

alter table REFERRAL_DETAILS drop column carespell_id;
alter table REFERRAL_DETAILS drop column item_index;

alter table carespell
  add constraint FK_carespell_referral_id
    foreign key (REFERRAL_ID)
      references REFERRAL_DETAILS (REFERRAL_ID);
