Home » SQL & PL/SQL » SQL & PL/SQL » Oracle: create a partition by reference (partition by reference(fk)) on non referenced table
Oracle: create a partition by reference (partition by reference(fk)) on non referenced table [message #681800] Tue, 01 September 2020 03:10 Go to next message
Messages: 1
Registered: September 2020
Junior Member
We have realized that we need to use Oracle (12 c) interval partitioning.We have an hierarchical entity model with lots of @OneToMany relationships.We want to use "partitioning by range" (day,month... ) on the "parent/root" entity (A) and "partition by reference" on all child entities (B). From the Oracle documentation: "Reference partitioning allows the partitioning of two tables related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints." The problem is that child entities (B) can refer to other entities ( C) that they don't have any link with the "parent/root" entity (A). I can create partitions on A and on B but when I want to drop the partition on A ( partition on B on cascade), I get an error integrity error and it fails. it works only if I delete all records on C and B and then partition them. I don't want to do that as it's not efficient and slow compared to dropping partitions directly

Please is there a way to create a partition on table C based on A(creation_date) without adding any foreign constraint between A and C?

Small example to illustrate the case

A - parent entity
B - child entity to A
C - child entity to B

create table
A (
id number primary key,
creation_date date
partition by range (creation_date)
partition p1 values less than (to_date('20180501','yyyymmdd')),

create table
B (
id number primary key,
value varchar2(5),
a_id number not null,
constraint fk_ba foreign key (a_id) references A
partition by reference(fk_ba);

create table
C (
id number primary key,
code varchar2(5),
b_id number not null,
constraint fk_cb foreign key (b_id) references B
Re: Oracle: create a partition by reference (partition by reference(fk)) on non referenced table [message #681801 is a reply to message #681800] Tue, 01 September 2020 04:57 Go to previous message
John Watson
Messages: 8547
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read

Is this what you want to do:

orclz> ed
Wrote file afiedt.buf

  1  create table
  2  C (
  3  id number primary key,
  4  code varchar2(5),
  5  b_id number not null,
  6  constraint fk_cb foreign key (b_id) references B
  7  )
  8* partition by reference (fk_cb)
orclz> /

Table created.

(ps - to save anyone else some minor irritation, OP has an extra comma at the end of the penultimate line for creating A)
Previous Topic: Oracle Procedure with table name as parameter
Next Topic: xml to json conversion
Goto Forum:

Current Time: Wed May 12 15:47:33 CDT 2021