-- Migration from 141-142

-- fix problems from 139-140 migration
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_AG_PERS_ARC_CK CHECK ((AG_ID_GRANTEE IS NOT NULL AND PERS_ID_GRANTEE IS NULL) OR (AG_ID_GRANTEE IS NULL AND PERS_ID_GRANTEE IS NOT NULL));
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_BK_UK UNIQUE(CODE);
ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_BK_UK UNIQUE(CODE);
ALTER TABLE SAMPLE_TYPES ADD CONSTRAINT SATY_BK_UK UNIQUE(CODE);
ALTER TABLE DATA_SET_TYPES ADD CONSTRAINT DSTY_BK_UK UNIQUE(CODE);
ALTER TABLE SPACES ADD CONSTRAINT SPACE_BK_UK UNIQUE(CODE);

DROP INDEX SCRIPT_DBIN_FK_I;
ALTER TABLE SCRIPTS DROP CONSTRAINT SCRI_DBIN_FK;
ALTER TABLE SCRIPTS DROP CONSTRAINT SCRI_UK;
ALTER TABLE SCRIPTS DROP COLUMN DBIN_ID;
ALTER TABLE SCRIPTS ADD CONSTRAINT SCRI_UK UNIQUE(NAME);

ALTER TABLE QUERIES DROP CONSTRAINT QUER_BK_UK;
ALTER TABLE QUERIES ADD CONSTRAINT QUER_BK_UK UNIQUE(NAME);
ALTER TABLE QUERIES DROP CONSTRAINT QUER_DBIN_FK;
ALTER TABLE QUERIES DROP COLUMN DBIN_ID;

DROP INDEX FILT_DBIN_FK_I;
ALTER TABLE FILTERS DROP CONSTRAINT FILT_DBIN_FK;
ALTER TABLE FILTERS DROP CONSTRAINT FILT_BK_UK;
ALTER TABLE FILTERS ADD CONSTRAINT FILT_BK_UK UNIQUE(NAME, GRID_ID);
ALTER TABLE FILTERS DROP COLUMN DBIN_ID;

DROP INDEX GRID_CUSTOM_COLUMNS_DBIN_FK_I;
ALTER TABLE GRID_CUSTOM_COLUMNS DROP CONSTRAINT GRID_CUSTOM_COLUMNS_DBIN_FK;
ALTER TABLE GRID_CUSTOM_COLUMNS DROP CONSTRAINT GRID_CUSTOM_COLUMNS_BK_UK;
ALTER TABLE GRID_CUSTOM_COLUMNS ADD CONSTRAINT GRID_CUSTOM_COLUMNS_BK_UK UNIQUE(CODE, GRID_ID);
ALTER TABLE GRID_CUSTOM_COLUMNS DROP COLUMN DBIN_ID;

ALTER TABLE PROPERTY_TYPES DROP CONSTRAINT PRTY_DBIN_FK;
ALTER TABLE PROPERTY_TYPES DROP CONSTRAINT PRTY_BK_UK;
ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_BK_UK UNIQUE(CODE,IS_INTERNAL_NAMESPACE);
ALTER TABLE PROPERTY_TYPES DROP COLUMN DBIN_ID;

ALTER TABLE CONTROLLED_VOCABULARIES DROP CONSTRAINT COVO_DBIN_FK;
ALTER TABLE CONTROLLED_VOCABULARIES DROP CONSTRAINT COVO_BK_UK;
ALTER TABLE CONTROLLED_VOCABULARIES ADD CONSTRAINT COVO_BK_UK UNIQUE(CODE,IS_INTERNAL_NAMESPACE);
ALTER TABLE CONTROLLED_VOCABULARIES DROP COLUMN DBIN_ID;

ALTER TABLE FILE_FORMAT_TYPES DROP CONSTRAINT FFTY_DBIN_FK;
ALTER TABLE FILE_FORMAT_TYPES DROP CONSTRAINT FFTY_BK_UK;
ALTER TABLE FILE_FORMAT_TYPES ADD CONSTRAINT FFTY_BK_UK UNIQUE(CODE);
ALTER TABLE FILE_FORMAT_TYPES DROP COLUMN DBIN_ID;

ALTER TABLE EXTERNAL_DATA_MANAGEMENT_SYSTEMS DROP CONSTRAINT EDMS_DBIN_FK;
ALTER TABLE EXTERNAL_DATA_MANAGEMENT_SYSTEMS DROP CONSTRAINT EDMS_CODE_UK;
ALTER TABLE EXTERNAL_DATA_MANAGEMENT_SYSTEMS ADD CONSTRAINT EDMS_CODE_UK UNIQUE(CODE);
ALTER TABLE EXTERNAL_DATA_MANAGEMENT_SYSTEMS DROP COLUMN DBIN_ID;

ALTER TABLE RELATIONSHIP_TYPES DROP CONSTRAINT RETY_UK;
ALTER TABLE RELATIONSHIP_TYPES ADD CONSTRAINT RETY_UK UNIQUE(CODE);
ALTER TABLE RELATIONSHIP_TYPES DROP COLUMN DBIN_ID;