IG-Foreign Key add issue at DB Migration

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• CA Identity Suite

Issue/Introduction

We are having database migration and as part of migration we are not able to add foreign key for  QRTZ_BLOB_TRIGGERS and QRTZ_TRIGGERS tables. In our investigation, we have found that it is because of data type difference on primary key (TRIGGER_NAME).

In below table structure we can see the datatype for TRIGGER_NAME in QRTZ_TRIGGERS table is NVARCHAR2 and datatype for TRIGGER_NAME in QRTZ_BLOB_TRIGGERS table is VARCHAR2.

Could you please help us to understand why datatype is different for primary key and what will be impact on Identity Governance application if it's not available.

Please see below table structure and error details for more details.

ORA-39083: Object type REF_CONSTRAINT:"IG_SDB"."SYS_C0014985" failed to create with error:
ORA-02267: column type incompatible with referenced column type

 

Failing sql is:
ALTER TABLE "IG_SDB"."QRTZ_BLOB_TRIGGERS" ADD FOREIGN KEY ("TRIGGER_NAME", "TRIGGER_GROUP")
REFERENCES "IG_SDB"."QRTZ_TRIGGERS" ("TRIGGER_NAME", "TRIGGER_GROUP") ENABLE

 

SQL> desc "IG_SDB"."QRTZ_TRIGGERS"
Name Null? Type
----------------------------------------- -------- ----------------------------
TRIGGER_NAME NOT NULL NVARCHAR2(80)
TRIGGER_GROUP NOT NULL NVARCHAR2(80)
JOB_NAME NOT NULL NVARCHAR2(80)
JOB_GROUP NOT NULL NVARCHAR2(80)
IS_VOLATILE NOT NULL VARCHAR2(1 CHAR)
DESCRIPTION NVARCHAR2(120)
NEXT_FIRE_TIME NUMBER(13)
PREV_FIRE_TIME NUMBER(13)
TRIGGER_STATE NOT NULL VARCHAR2(16 CHAR)
TRIGGER_TYPE NOT NULL VARCHAR2(8 CHAR)
START_TIME NOT NULL NUMBER(13)
END_TIME NUMBER(13)
CALENDAR_NAME VARCHAR2(80 CHAR)
MISFIRE_INSTR NUMBER(2)
JOB_DATA BLOB

 

SQL> desc "IG_SDB"."QRTZ_BLOB_TRIGGERS"
Name Null? Type
----------------------------------------- -------- ----------------------------
TRIGGER_NAME NOT NULL VARCHAR2(80 CHAR)
TRIGGER_GROUP NOT NULL VARCHAR2(80 CHAR)
BLOB_DATA BLOB

Environment

Release : 14.2

Component : CA IDENTITY SUITE (VIRTUAL APPLIANCE)

Resolution

Dropped qrtz_blob_triggers
it had data types pointing to varchar2

Recreated with:
CREATE TABLE qrtz_blob_triggers
(
TRIGGER_NAME NVARCHAR2(80) NOT NULL,
TRIGGER_GROUP NVARCHAR2(80) NOT NULL,
BLOB_DATA BLOB NULL,
PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP),
FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)
REFERENCES QRTZ_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP)
)

Part of that command creates the foreign key

We did not need to import data to qrtz_blob_triggers, the table was empty

L2 states that there should be no issues on ear restart, as the table exists and will not need to be recreated.