Some time ago, I was analyzing the tables in a system to determine which tables should be partitioned, and the best partitioning key and method for each of them. For some tables, I decided to go with REF partitioning.
Then I built a script to automate the process, and while testing I came across some issues. You may know, to be able to create a REF partitioning table, both the primary key on the parent table and the foreign key on the child table must be enabled and validated. Additionally, the foreign key column must be NOT NULL. Since our model had some constraints as NOVALIDATE and also some FK columns accepting nulls, I first had to fix this in order to continue.
But, event after doing all this fixes, for some tables I was still receiving the following error: “ORA-14652: reference partitioning foreign key is not supported“.
After some more investigation, I found the problem: some of the NOT NULL constraints were created as CHECK constraints directly. And here starts my demo today 🙂 .
Have you realized that actually all the NOT NULL constraints you create are actually created as CHECK constraints?
Basically, you can create NOT NULL constraints in three different ways:
create table T1 (
not_null_no_name number not null,
not_null_name number constraint nnn not null,
not_null_check number constraint nnc check (not_null_check is not null)
);
Table created.
desc T1
Name Null? Type
----------------------------------------- -------- ----------------------------
NOT_NULL_NO_NAME NOT NULL NUMBER
NOT_NULL_NAME NOT NULL NUMBER
NOT_NULL_CHECK NUMBER
Notice that I create three constraints that avoid NULL values in the columns: the first simply mentions NOT NULL after the data type (the most common way I would say); the second do the same, but I put a name for the constraint; and the third I created as a CHECK constraint directly, also avoiding NULL values. Also notice that the first two show up when a issue a describe of the table, but not the third.
Nevertheless, all three do avoid NULL values:
insert into T1 values (NULL,1,1);
insert into T1 values (NULL,1,1)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("CLAROE"."T1"."NOT_NULL_NO_NAME")
insert into T1 values (1,NULL,1);
insert into T1 values (1,NULL,1)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("CLAROE"."T1"."NOT_NULL_NAME")
insert into T1 values (1,1,NULL);
insert into T1 values (1,1,NULL)
*
ERROR at line 1:
ORA-02290: check constraint (CLAROE.NNC) violated
Note that while the two created as simple NOT NULL show a message referring that you tried to insert NULL, while the third, created as CHECK, simply mentions you violated the constraint.
Let’s see how these constraints show up in the data dictionary:
col constraint_name for a15
col search_condition for a30
select constraint_name, constraint_type, status, validated, search_condition
from user_constraints where table_name = 'T1';
CONSTRAINT_NAME C STATUS VALIDATED SEARCH_CONDITION
--------------- - -------- ------------- ------------------------------
SYS_C002171496 C ENABLED VALIDATED "NOT_NULL_NO_NAME" IS NOT NULL
NNN C ENABLED VALIDATED "NOT_NULL_NAME" IS NOT NULL
NNC C ENABLED VALIDATED not_null_check is not null
3 rows selected.
As I mentioned, all the three constraints were created as CHECK constraints in the background. Oracle created a name for the one I didn’t name. Note the search condition is the same, with the only difference that the ones created as NOT NULL are formatted, while the CHECK is exactly as I typed.
Ok, this is the situation I had in my environment as I mentioned in the beginning.
Now I will try to REF partition the table using as key each of the three columns and see what happens. But first I need to have a parent table, the foreign keys and some data in both tables:
create table PARENT_T1 (id number primary key)
partition by range (id)
(partition p1 values less than (5),
partition p2 values less than (MAXVALUE));
Table created.
alter table T1 add (
constraint FK_not_null_no_name FOREIGN KEY (not_null_no_name) references PARENT_T1,
constraint FK_not_null_name FOREIGN KEY (not_null_name ) references PARENT_T1,
constraint FK_not_null_check FOREIGN KEY (not_null_check ) references PARENT_T1
);
Table altered.
insert into PARENT_T1 select rownum from dual connect by level <= 10;
10 rows created.
insert into T1 select mod(rownum,10)+1, mod(rownum,10)+1, mod(rownum,10)+1 from dual connect by level <= 100;
100 rows created.
commit;
Commit complete.
Now let’s try to partition with each of the columns:
alter table T1 modify partition by reference (FK_not_null_no_name) ;
Table altered.
alter table T1 modify partition by reference (FK_not_null_name) ;
Table altered.
alter table T1 modify partition by reference (FK_not_null_check) ;
alter table T1 modify partition by reference (FK_not_null_check)
*
ERROR at line 1:
ORA-14652: reference partitioning foreign key is not supported
Aha! So, for the two constraints created as simple NOT NULL, they worked for the REF partitioning. But the one created as CHECK failed! Despite it indeed avoid NULL values, it’s not embedded in the table structure and this is required for the REF partitioning.
If I simply remove the CHECK constraint a modify the column to NOT NULL, then I can REF partition by that third column:
alter table t1 drop constraint nnc;
Table altered.
alter table t1 modify (not_null_check not null);
Table altered.
alter table T1 modify partition by reference (FK_not_null_check) ;
Table altered.
Now it worked as expected!
And that’s it. I suppose there are other things that would not work well if you have a CHECK constraint instead of a real NOT NULL constraint. Let me know if you have faced similar or other issues related to it.
Enjoy, see you soon!
EDUARDO CLARO 