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
Release : 14.2
Component : CA IDENTITY SUITE (VIRTUAL APPLIANCE)
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.