OraFAQ Forum: SQL & PL/SQL » ORA-14652: reference partitioning FOREIGN KEY IS NOT supported

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-14652: reference partitioning FOREIGN KEY IS NOT supported (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-14652: reference partitioning FOREIGN KEY IS NOT supported [message #501572]

Wed, 30 March 2011 00:20

rangan.s
Messages: 75
Registered: February 2008
Location: chennai

Member

HI,

I am trying to use reference partitioning the parent table records moved using the row movement . but the child table records are not moving below is my sample .

CREATE TABLE ref_parent1 (
table_name VARCHAR2(30),
order_date DATE,
num_rows NUMBER);

INSERT INTO ref_parent1 VALUES ('TEST',SYSDATE,100);
INSERT INTO ref_parent1 VALUES ('TEST1',SYSDATE,1000);
INSERT INTO ref_parent1 VALUES ('TEST2',SYSDATE,1000);
COMMIT;

CREATE TABLE ref_parent (
table_name1 ,
order_date ,
num_rows )
PARTITION BY RANGE(num_rows) (
PARTITION num_rows1 VALUES LESS THAN (100) ,
PARTITION num_rows2 VALUES LESS THAN (1000),
PARTITION num_rows3 VALUES LESS THAN (10000) ,
PARTITION num_rows4 VALUES LESS THAN (MAXVALUE))
ENABLE ROW MOVEMENT AS
SELECT * FROM ref_parent1;

ALTER TABLE ref_parent ADD CONSTRAINT pk_ref_parent PRIMARY KEY (table_name1) USING INDEX;

CREATE TABLE ref_child1 (
table_name VARCHAR2(30) NOT NULL,
index_name VARCHAR2(30) NOT NULL
);

INSERT INTO ref_child1 VALUES ('TEST','IDX1');
INSERT INTO ref_child1 VALUES ('TEST1','IDX2');
INSERT INTO ref_child1 VALUES ('TEST2','IDX3');
COMMIT;

CREATE TABLE ref_child (
table_name ,
index_name ,
CONSTRAINT fk_ref_child_parent
FOREIGN KEY(table_name) REFERENCES ref_parent(table_name1))
PARTITION BY REFERENCE(fk_ref_child_parent)
ENABLE ROW MOVEMENT AS
SELECT * FROM ref_parent;

ORA-14652: reference partitioning FOREIGN KEY IS NOT supported

Thanks
Rangan Sampath

Report message to a moderator

Re: ORA-14652: reference partitioning FOREIGN KEY IS NOT supported [message #501591 is a reply to message #501572]

Wed, 30 March 2011 01:09

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-14652: reference partitioning foreign key is not supported
 *Cause:  The specified partitioning foreign key was not supported
          for reference-partitioned tables. All columns of the
          partitioning foreign key must be constrained NOT NULL with
          enabled, validated, and not deferrable constraints. Furthermore,
          a virtual column cannot be part of the partitioning foreign key.
* Action: Correct the statement to specify a supported
          partitioning foreign key.

Regards
Michel

Report message to a moderator

Re: ORA-14652: reference partitioning FOREIGN KEY IS NOT supported [message #501595 is a reply to message #501572]

Wed, 30 March 2011 01:22

rangan.s
Messages: 75
Registered: February 2008
Location: chennai

Member

i dont think so i got proper solution from the answer which you posted .

Dont say something instead of solution. I can say its a bug from Oracle 11g

can anyone confirm from oracle technical team ???

Thanks
Rangan Sampath

Report message to a moderator

Re: ORA-14652: reference partitioning FOREIGN KEY IS NOT supported [message #501606 is a reply to message #501595]

Wed, 30 March 2011 01:58

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As you cannot specify the NOT NULL property on a CTAS, you have to do it in 2 steps:

SQL> CREATE TABLE ref_child (
  2  table_name varchar2(30) not null,
  3  index_name varchar2(30),
  4  CONSTRAINT fk_ref_child_parent
  5  FOREIGN KEY(table_name) REFERENCES ref_parent(table_name1))
  6  PARTITION BY REFERENCE(fk_ref_child_parent)
  7  ENABLE ROW MOVEMENT ;

Table created.

SQL> insert into ref_child select * from ref_child1 ;

3 rows created.

You can create a SR to file it as a bug as Oracle guarantees to create the NOT NULL constraint.
Unfortunatly I bet it makes all checks that the table can be created and before checking the source table parameters and knowing there is a NOT NULL constraint on the copied column.

Regards
Michel

Report message to a moderator

Re: ORA-14652: reference partitioning FOREIGN KEY IS NOT supported [message #501607 is a reply to message #501595]

Wed, 30 March 2011 02:02

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

can anyone confirm from oracle technical team ??

There is no-one from Oracle technical team here; even if there was one he/she could not answer this question.

You have to go with a SR.
I hope you will be able to make them create a patch for this; you have a clear, simple and reproducible test case.

Regards
Michel

CM: first sentence should read "There is no-one..."

[Updated on: Mon, 04 April 2011 01:50] by Moderator

Report message to a moderator

Previous Topic:	Not able to insert into another table (merged 3)
Next Topic:	Enable to run procedure through job

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 01 21:55:28 CDT 2025