DECLARE
  DROP_FOREIGN_KEY_IOP VARCHAR2(100);
BEGIN
  SELECT CONSTRAINT_NAME
  INTO DROP_FOREIGN_KEY_IOP
  FROM USER_CONSTRAINTS
  WHERE TABLE_NAME = 'IDN_OIDC_PROPERTY' AND CONSTRAINT_TYPE = 'R';

  EXECUTE IMMEDIATE 'ALTER TABLE IDN_OIDC_PROPERTY DROP CONSTRAINT ' || DROP_FOREIGN_KEY_IOP;
END;
/

DECLARE
  DROP_FOREIGN_KEY_IOCAC VARCHAR2(100);
BEGIN
  SELECT CONSTRAINT_NAME
  INTO DROP_FOREIGN_KEY_IOCAC
  FROM USER_CONSTRAINTS
  WHERE TABLE_NAME = 'IDN_OAUTH2_CIBA_AUTH_CODE' AND CONSTRAINT_TYPE = 'R';

  EXECUTE IMMEDIATE 'ALTER TABLE IDN_OAUTH2_CIBA_AUTH_CODE DROP CONSTRAINT ' || DROP_FOREIGN_KEY_IOCAC;
END;
/

DROP INDEX IDX_IOP_CK
/

ALTER TABLE IDN_OAUTH_CONSUMER_APPS DROP CONSTRAINT CONSUMER_KEY_CONSTRAINT
/

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)
/
