Ausgangslage

Ein beliebiges Constraint kann nicht aktiviert werden, da es die Constraint Regel verletzt. Die folgende Anleitung zeigt, wie die Row(s) gefunden werden können welche das Constraint verletzen.

Beispiel

Tabelle:    CREDIT (Master)
Tabelle:    ORDER_REQ (Detail)
Constraint: Foreign Key von ORDER_REQ nach CREDIT
            FK_ORDER_REQ_CREDIT

Exception Table kreieren

CREATE TABLE except_table (row_id     ROWID,
                           owner      VARCHAR2(30),
                           table_name VARCHAR2(30),
                           constraint VARCHAR2(30));

Constraint Verletzung in except_table protokollieren

ALTER TABLE order_req ENABLE
  CONSTRAINT FK_ORDER_REQ_CREDIT
  EXCEPTIONS INTO except_table;

ORA-02298: cannot enable (PPB.FK_ORDER_REQ_CREDIT) - parent keys not found

Rows, welche Constraint verletzen auslisten

SELECT DBMS_ROWID.ROWID_TO_RESTRICTED(ROWID,0)    /* Oracle 8 */
 FROM except_table;

SELECT row_id FROM except_table;  /* Oracle 7 */

DBMS_ROWID.ROWID_T
------------------
000234DB.0000.0005
000234DB.0001.0005

Finden der entsprechenden Rows in der Originaltabelle

SELECT TO_CHAR(create_date,'DD.MM.YYYY:HH24:MI:SS'),origin_id,ms_id
  FROM order_req
 WHERE ROWID IN ('AAAAqTAAFAAAaDLAAA','AAAAqTAAFAAAap5AAf');

TO_CHAR(CREATE_DATE ORIGIN_ID      MS_ID
------------------- ---------- ---------
14.02.1999:17:45:55 SUP03DEN1     930248
13.02.1999:17:07:56 VP258951      937928

SELECT TO_CHAR(create_date,'DD.MM.YYYY:HH24:MI:SS'),origin_id,ms_id
FROM order_req
WHERE TO_CHAR(create_date,'DD.MM.YYYY:HH24:MI:SS') = '14.02.1999:17:45:55'
  AND origin_id = 'SUP03DEN1'
  AND ms_id = 930248;

TO_CHAR(CREATE_DATE ORIGIN_ID      MS_ID
------------------- ---------- ---------
14.02.1999:17:45:55 SUP03DEN1     930248

SELECT TO_CHAR(create_date,'DD.MM.YYYY:HH24:MI:SS'),origin_id,ms_id
FROM order_req
WHERE TO_CHAR(create_date,'DD.MM.YYYY:HH24:MI:SS') = '13.02.1999:17:07:56'
  AND origin_id = 'VP258951'
  AND ms_id = 937928;

TO_CHAR(CREATE_DATE ORIGIN_ID      MS_ID
------------------- ---------- ---------
13.02.1999:17:07:56 VP258951      937928

Finden der entsprechenden Master-Row in der Parenttabelle

SELECT TO_CHAR(create_date,'DD.MM.YYYY:HH24:MI:SS'),origin_id,ms_id
FROM credit
WHERE TO_CHAR(create_date,'DD.MM.YYYY:HH24:MI:SS') = '14.02.1999:17:45:55'
  AND origin_id = 'SUP03DEN1'
  AND ms_id = 930248;

no rows selected

Löschen der rows in der Detailtabelle

DELETE FROM order_req
WHERE TO_CHAR(create_date,'DD.MM.YYYY:HH24:MI:SS') = '14.02.1999:17:45:55'
  AND origin_id = 'SUP03DEN1'
  AND ms_id = 930248;

DELETE FROM order_req
WHERE TO_CHAR(create_date,'DD.MM.YYYY:HH24:MI:SS') = '13.02.1999:17:07:56'
AND origin_id = 'VP258951'
AND ms_id = 937928;

COMMIT;

Constraint einschalten

ALTER TABLE order_req ENABLE
  CONSTRAINT FK_ORDER_REQ_CREDIT;