Oracle sqlldr: Constraints are nor reenabled after finishing batch loading

Question

I have the following table in my Oracle database (19c):

CREATE TABLE debtors (
    bankruptID NUMBER NOT NULL,
    category VARCHAR2(50) NOT NULL,
    lastname VARCHAR2(100),
    firstname VARCHAR2(80),
    birthdate DATE,
    birthplace VARCHAR2(100),
    constraint DEBTORS_PK PRIMARY KEY (bankruptID));
    
ALTER TABLE debtors ADD CONSTRAINT debtors_fk0 FOREIGN KEY (category) REFERENCES categories(BankruptCategory);
    

It has a primary key and a foreign key to another table. Into this table (debtors) I want to import data from a csv file using sqlldr. Here are the ctl and *par *files:

OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET CL8MSWIN1251
INTO TABLE myschema.debtors
REENABLE DISABLED_CONSTRAINTS EXCEPTIONS EXCEPT_TABLE
FIELDS TERMINATED BY '^'
TRAILING NULLCOLS
(
bankruptID,
category,
lastname,
firstname,
birthdate date 'YYYY-MM-DD HH24:MI:SS',
birthplace
)

userid=username/password@mydb
control=debtors.ctl
log=debtors.log
bad=debtors.bad
data=debtors.csv
direct=true

After completing the task the log file states the following:

Referential Integrity Constraint/Trigger Information: NULL, UNIQUE, and PRIMARY KEY constraints are unaffected.

Constraint mychema.DEBTORS.DEBTORS_FK0 was disabled and novalidated before the load. The following index(es) on table mychema.DEBTORS were processed: index mychema.DEBTORS_PK loaded successfully with 896 keys

Table mychema.DEBTORS has no constraint exception table. No CHECK, REFERENTIAL constraints were re-enabled after the load.

And the foreign key (debtors_fk0) turns disabled. As you can see in the *ctl *file i have the REENABLE clause but it appears not to be working. Could you please help me undestand what the problem is? I want it to reenable the constraint automatically

I created an EXCEPT_TABLE table to store all exceptions, but it didn't help

Answer 1

Everything is as expected. If you checked constraint status, you'd see that it is ENABLED, but NOT VALIDATED - it can't be if there are rows that violate foreign key constraint.

---

Example 1: what happens when everything is OK?

Sample tables:

SQL> create table except_table
  2    (row_id     rowid,
  3     owner      varchar2(128),
  4     table_name varchar2(128),
  5     constraint varchar2(128)
  6    );

Table created.

Two categories (22, 33) - they will BOTH be used while loading data, which means that foreign key constraint won't be violated:

SQL> create table category
  2    (id_cat number primary key);

Table created.

SQL> insert into category values (22);

1 row created.

SQL> insert into category values (33);

1 row created.

SQL> create table test
  2    (id_test     number constraint pk_t primary key,
  3     id_cat      number constraint fk_tc references category,
  4     debit       number
  5    );

Table created.

SQL>

Control file:

load data
infile *
replace
into table test
reenable disabled_constraints exceptions except_table
fields terminated by '|'
trailing nullcols
( id_test,
  id_cat,
  debit
)

begindata
1|22|456
2|33|777

Loading session: with direct path, Oracle automatically disables constraints as described in documentation.

SQL>  $sqlldr scott/tiger@pdb1 control=test15.ctl log=test15.log direct=true

SQL*Loader: Release 21.0.0.0.0 - Production on Sat Aug 19 21:38:05 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 2.

Table TEST:
  2 Rows successfully loaded.

Check the log file:
  test15.log
for more information about the load.

SQL>

Log file says:

Referential Integrity Constraint/Trigger Information:
NULL, UNIQUE, and PRIMARY KEY constraints are unaffected.

Constraint TEST.FK_TC was disabled and novalidated before the load.
The following index(es) on table TEST were processed:
index SCOTT.PK_T loaded successfully with 2 keys
TEST.FK_TC was re-enabled.

Table TEST has constraint exception table EXCEPT_TABLE.
Constraint TEST.FK_TC was validated

Result:

SQL> select * from test;

   ID_TEST     ID_CAT      DEBIT
---------- ---------- ----------
         1         22        456
         2         33        777

SQL> select * From except_table;

no rows selected

SQL> select constraint_type, table_name, status, validated
  2  From user_Constraints
  3  where constraint_name = 'FK_TC';

C TABLE_NAME      STATUS   VALIDATED
- --------------- -------- -------------
R TEST            ENABLED  VALIDATED        --> as everything went OK, constraint
                                                is enabled and validated
  
SQL>

---

Example #2: missing foreign key constraint's parent key.

Sample tables:

SQL> drop table except_table;

Table dropped.

SQL> drop table test;

Table dropped.

SQL> drop table category;

Table dropped.

SQL> create table except_table
  2    (row_id     rowid,
  3     owner      varchar2(128),
  4     table_name varchar2(128),
  5     constraint varchar2(128)
  6    );

Table created.

Category is now missing ID_CAT = 33:

SQL> create table category
  2    (id_cat number primary key);

Table created.

SQL> insert into category values (22);

1 row created.

SQL> create table test
  2    (id_test     number constraint pk_t primary key,
  3     id_cat      number constraint fk_tc references category,
  4     debit       number
  5    );

Table created.

SQL>

Control file is unmodified - sample data still contains ID_CAT = 33 row.

load data
infile *
replace
into table test
reenable disabled_constraints exceptions except_table
fields terminated by '|'
trailing nullcols
( id_test,
  id_cat,
  debit
)

begindata
1|22|456
2|33|777

Nothing changed in a way sqlldr is being called; both rows (even the invalid one!) are loaded:

SQL>  $sqlldr scott/tiger@pdb1 control=test15.ctl log=test15.log direct=true

SQL*Loader: Release 21.0.0.0.0 - Production on Sat Aug 19 21:44:00 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 2.

Table TEST:
  2 Rows successfully loaded.

Check the log file:
  test15.log
for more information about the load.

SQL>

Log says this (read it carefully!):

Referential Integrity Constraint/Trigger Information:
NULL, UNIQUE, and PRIMARY KEY constraints are unaffected.

Constraint TEST.FK_TC was disabled and novalidated before the load.
The following index(es) on table TEST were processed:
index SCOTT.PK_T loaded successfully with 2 keys
TEST.FK_TC was re-enabled.

Table TEST has constraint exception table EXCEPT_TABLE.
TEST.FK_TC was not re-validated due to ORACLE error.
ORA-02298: cannot validate (SCOTT.FK_TC) - parent keys not found

Result: both rows in test (target) table. except_Table now contains row which violated foreign key constraint. Constraint status is ENABLED NOT VALIDATED.

SQL> select * from test;

   ID_TEST     ID_CAT      DEBIT
---------- ---------- ----------
         1         22        456
         2         33        777

SQL> select * From except_table;

ROW_ID             OWNER      TABLE_NAME      CONSTRAINT
------------------ ---------- --------------- ---------------
AAAZRjAAMAAAASbAAB SCOTT      TEST            FK_TC

SQL> select constraint_type, table_name, status, validated
  2  From user_Constraints
  3  where constraint_name = 'FK_TC';

C TABLE_NAME      STATUS   VALIDATED
- --------------- -------- -------------
R TEST            ENABLED  NOT VALIDATED

SQL>

If you try to manually validate foreign key constraint, you won't be able to do that:

SQL> alter table test modify constraint fk_tc enable validate;
alter table test modify constraint fk_tc enable validate
                                   *
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_TC) - parent keys not found


SQL>

First remove row(s) that violated the constraint, then validate it:

SQL> delete from test
  2  where not exists (select null from category
  3                    where category.id_cat = test.id_cat);

1 row deleted.

SQL> alter table test modify constraint fk_tc enable validate;

Table altered.

SQL>

---

Therefore, yes - everything is OK and works as expected.