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
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.