CREATE ALIAS IF NOT EXISTS DROP_FOREIGN_KEY_IOP AS $$ void executeSql(Connection conn, String sql) throws SQLException { conn.createStatement().executeUpdate(sql); } $$;

call DROP_FOREIGN_KEY_IOP('ALTER TABLE IDN_OIDC_PROPERTY DROP CONSTRAINT ' || (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'IDN_OIDC_PROPERTY' AND COLUMN_NAME = 'CONSUMER_KEY'));

DROP ALIAS IF EXISTS DROP_FOREIGN_KEY_IOP;

CREATE ALIAS IF NOT EXISTS DROP_FOREIGN_KEY_IOCAC AS $$ void executeSql(Connection conn, String sql) throws SQLException { conn.createStatement().executeUpdate(sql); } $$;

call DROP_FOREIGN_KEY_IOCAC('ALTER TABLE IDN_OAUTH2_CIBA_AUTH_CODE DROP CONSTRAINT ' || (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'IDN_OAUTH2_CIBA_AUTH_CODE' AND COLUMN_NAME = 'CONSUMER_KEY'));

DROP ALIAS IF EXISTS DROP_FOREIGN_KEY_IOCAC;

DROP INDEX IDX_IOP_CK ON IDN_OIDC_PROPERTY;

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