USE `akura`;
/*!40014 SET UNIQUE_CHECKS=0 */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;

--
-- modification for foreign key fk_SUBJECT_STREAM1 in SUBJECT table.
--

ALTER TABLE SUBJECT DROP FOREIGN KEY `fk_SUBJECT_STREAM1` ;

ALTER TABLE SUBJECT 
  ADD CONSTRAINT fk_SUBJECT_STREAM1
  FOREIGN KEY (`STREAM_ID` )
  REFERENCES STREAM (`STREAM_ID` )
  ON DELETE RESTRICT
  ON UPDATE NO ACTION;

--
-- Add goverment SUBJECT code field.
--

ALTER TABLE SUBJECT ADD COLUMN `GOV_SUBJECT_CODE` VARCHAR(10) NULL DEFAULT NULL;

ALTER TABLE SUBJECT ADD UNIQUE INDEX `GOV_SUBJECT_CODE_UNIQUE` (`GOV_SUBJECT_CODE` ASC) ;

--
-- Add "SPONSORSHIP" column to SCHOLARSHIP table.
--

ALTER TABLE SCHOLARSHIP ADD COLUMN `SPONSORSHIP` VARCHAR(45) NULL DEFAULT NULL  AFTER `NO_OF_STUDENTS` ;


ALTER TABLE STAFF ADD COLUMN `EMERGENCY_CONTACT_NAME` VARCHAR(45) NULL  AFTER `COUNTRY_ID` , ADD COLUMN `EMERGENCY_MOBILE_NO` VARCHAR(45) NULL  AFTER `EMERGENCY_CONTACT_NAME` , ADD COLUMN `EMERGENCY_RESIDENCE_NO` VARCHAR(45) NULL  AFTER `EMERGENCY_MOBILE_NO` , ADD COLUMN `INSURANCE_POLICY_DETAILS` VARCHAR(450) NULL  AFTER `EMERGENCY_RESIDENCE_NO` , ADD COLUMN `INTEREST` VARCHAR(450) NULL  AFTER `INSURANCE_POLICY_DETAILS` ;

--
-- Add "COUNTRY_CODE" column to COUNTRY table.
--

ALTER TABLE COUNTRY ADD COLUMN `COUNTRY_CODE` VARCHAR(3) NOT NULL AFTER `MODIFIED_TIME`;

--
-- Change "SCHOOL" table `PRIMARY_AND_SECONDARY_SCHOOL_INFO` and `FACILITIES` column lengths.
--

ALTER TABLE SCHOOL MODIFY `PRIMARY_AND_SECONDARY_SCHOOL_INFO` VARCHAR(350);
ALTER TABLE SCHOOL MODIFY `FACILITIES` VARCHAR(350);

/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
/*!40014 SET UNIQUE_CHECKS=1 */;