REG_PROPERTY and REG_RESOURCE_PROPERTY Table Cleanup Scripts Type 2

reg_property_cleanup_type2.sql

CREATE TABLE TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID INTEGER);

-- Extract resource property (ID) created when versioning is disabled --

INSERT INTO TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID)
   SELECT REG_PROPERTY_ID
   FROM REG_RESOURCE_PROPERTY
   WHERE REG_PATH_ID IN
       (SELECT REG_PATH_ID
        FROM REG_RESOURCE);

 -- Extract resource property (ID) created when versioning is enabled --

INSERT INTO TEMP_REG_RESOURCE_PROPERTY_ID(REG_PROPERTY_ID)
   SELECT REG_PROPERTY_ID
   FROM REG_RESOURCE_PROPERTY
   WHERE REG_VERSION IN
       (SELECT REG_VERSION
        FROM REG_RESOURCE);

-- Drop the foreign key constraint --

ALTER TABLE REG_RESOURCE_PROPERTY DROP FOREIGN KEY REG_RESOURCE_PROPERTY_FK_BY_TAG_ID;

 -- delete all unwanted REG_RESOURCE_PROPERTY entries --

DELETE
FROM REG_RESOURCE_PROPERTY
WHERE REG_PROPERTY_ID NOT IN
    (SELECT REG_PROPERTY_ID
     FROM TEMP_REG_RESOURCE_PROPERTY_ID);

 -- delete all unwanted REG_PROPERTY entries --

DELETE
FROM REG_PROPERTY
WHERE REG_ID NOT IN
    (SELECT REG_PROPERTY_ID
     FROM TEMP_REG_RESOURCE_PROPERTY_ID);

-- Insert back the foreign key constraint --

ALTER TABLE REG_RESOURCE_PROPERTY ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID);

 -- drop temporary table --

DROP TABLE TEMP_REG_RESOURCE_PROPERTY_ID;