BEGIN
 DECLARE STMT VARCHAR(200);
 FOR v AS cur1 CURSOR FOR
   select CONSTNAME from SYSCAT.TABCONST WHERE TABNAME='IDN_OIDC_PROPERTY' AND TYPE = 'F'
 DO
  SET STMT = 'ALTER TABLE IDN_OIDC_PROPERTY DROP FOREIGN KEY ' ||  v.CONSTNAME;
  PREPARE S1 FROM STMT;
  EXECUTE S1;
 END FOR;
END
/

BEGIN
 DECLARE STMT VARCHAR(200);
 FOR v AS cur1 CURSOR FOR
   select CONSTNAME from SYSCAT.TABCONST WHERE TABNAME='IDN_OAUTH2_CIBA_AUTH_CODE' AND TYPE = 'F'
 DO
  SET STMT = 'ALTER TABLE IDN_OAUTH2_CIBA_AUTH_CODE DROP FOREIGN KEY ' ||  v.CONSTNAME;
  PREPARE S1 FROM STMT;
  EXECUTE S1;
 END FOR;
END
/

BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
  BEGIN END;
  EXECUTE IMMEDIATE 'DROP INDEX IDX_IOP_CK';
END
/

ALTER TABLE IDN_OAUTH_CONSUMER_APPS DROP UNIQUE CONSUMER_KEY_CONSTRAINT
/

ALTER TABLE IDN_OAUTH_CONSUMER_APPS ALTER COLUMN TENANT_ID SET NOT NULL
/

CALL SYSPROC.ADMIN_CMD('REORG TABLE IDN_OAUTH_CONSUMER_APPS')
/

ALTER TABLE IDN_OAUTH_CONSUMER_APPS ADD CONSTRAINT CONSUMER_KEY_CONSTRAINT UNIQUE (TENANT_ID, CONSUMER_KEY)
/

ALTER TABLE IDN_OIDC_PROPERTY ADD CONSTRAINT FK_TENANT_CONSUMER_KEY_IOP_IOCA FOREIGN KEY (TENANT_ID, CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(TENANT_ID, CONSUMER_KEY) ON DELETE CASCADE
/

ALTER TABLE IDN_OAUTH2_CIBA_AUTH_CODE ADD CONSTRAINT FK_TENANT_CONSUMER_KEY_IOCAC_IOCA FOREIGN KEY (TENANT_ID, CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(TENANT_ID, CONSUMER_KEY) ON DELETE CASCADE
/

CREATE INDEX IDX_IOP_CK ON IDN_OIDC_PROPERTY(TENANT_ID, CONSUMER_KEY)
/
