/*Step 01*/

CREATE TABLE RM_ENVIRONMENT(
ID INTEGER NOT NULL ,
NAME VARCHAR(128) NOT NULL,
PRIMARY KEY (ID),
UNIQUE (NAME)
)ENGINE INNODB;

INSERT INTO RM_ENVIRONMENT (ID,NAME) VALUES (0,’DEFAULT’);

---------------------------------------------------------------------------------------

/* modify RM_SERVER_INSTANCE start */

ALTER TABLE RM_SERVER_INSTANCE
ADD COLUMN ENVIRONMENT_ID INTEGER NOT NULL AFTER ID;

/* modify composite unique key in RM_SERVER_INSTANCE */

SHOW INDEXES RM_SERVER_INSTANCE;

or

SHOW CREATE TABLE RM_SERVER_INSTANCE;

/* drop all the keys except primary which retrieve from above query */

ALTER TABLE RM_SERVER_INSTANCE DROP INDEX name_of_key_except_primary;

/* add composite unique key */

ALTER TABLE RM_SERVER_INSTANCE ADD UNIQUE (NAME, ENVIRONMENT_ID, TENANT_ID);

/* add foreign key constraint */

ALTER TABLE RM_SERVER_INSTANCE ADD CONSTRAINT FOREIGN KEY (ENVIRONMENT_ID) REFERENCES RM_ENVIRONMENT (ID) ON DELETE CASCADE ON UPDATE CASCADE;

update RM_SERVER_INSTANCE set INSTANCE_TYPE='SYSTEM';

commit;

/* modify RM_SERVER_INSTANCE end */

--------------------------------------------------------------------------------------

/* modify RM_DATABASE start */

SHOW CREATE TABLE RM_DATABASE;

/* drop foreign key constraint */
ALTER TABLE RM_DATABASE DROP FOREIGN KEY name_of_fk_condtraint;

/* add foreign key constraint */

ALTER TABLE RM_DATABASE ADD CONSTRAINT FOREIGN KEY (RSS_INSTANCE_ID) REFERENCES RM_SERVER_INSTANCE (ID) ON DELETE CASCADE ON UPDATE CASCADE;

update RM_DATABASE set TYPE='SYSTEM';

commit;

/* modify RM_DATABASE end */


--------------------------------------------------------------------------------------------------