Home » RDBMS Server » Server Administration » Dropped child partition retains fk constraint with parent partition after row movement (Oracle 10.2 RHEL4)
Dropped child partition retains fk constraint with parent partition after row movement [message #351931] Fri, 03 October 2008 14:13 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
We are instituting a new process where we will have two partitioned tables. The parent (test_part) and child (test_part_sources). When I drop the child partition, I update the partition key (part_date) on the parent table to move the row into a new partition. When I attempt to drop the parent partition, I get a foreign key constraint violation. I'm then able to drop the constraint, drop the parent partition, then re-enable the constraint.
Why does the foreign key constraint still exist if the data has moved to a new partition?
Is there any way I can manage this process without disabling/re-enabling the constraint?
Are there any major detractions with enabling row movement with a partition key?

Parent table:
SQL> create table test_part(
visitorid       number,
addeddate       date,
part_date       date,
constraint test_part_pk primary key(visitorid))
partition by range (part_date) (
partition tp200803 values less than (TO_DATE('2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tp200804 values less than (TO_DATE('2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tp200805 values less than (TO_DATE('2008-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tp200806 values less than (TO_DATE('2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tp200807 values less than (TO_DATE('2008-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tp200808 values less than (TO_DATE('2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tp200809 values less than (TO_DATE('2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tp200810 values less than (TO_DATE('2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition maxvalue values less than (maxvalue)
tablespace data_large); 


enable row movement using partition key:
SQL> alter table test_part enable row movement;

Table altered.


insert test data into parent table:
insert into test_part
values
(1,to_date('2008-02-23 00:00:00','SYYYY-MM-DD HH24:MI:SS'), to_date('2008-02-23 00:00:00','SYYYY-MM-DD HH24:MI:SS'));

insert into test_part
values
(2,to_date('2008-03-15 00:00:00','SYYYY-MM-DD HH24:MI:SS'), to_date('2008-03-15 00:00:00','SYYYY-MM-DD HH24:MI:SS'));

insert into test_part
values
(3,to_date('2008-03-23 00:00:00','SYYYY-MM-DD HH24:MI:SS'), to_date('2008-03-23 00:00:00','SYYYY-MM-DD HH24:MI:SS'));

insert into test_part
values
(4,to_date('2008-04-25 00:00:00','SYYYY-MM-DD HH24:MI:SS'), to_date('2008-04-25 00:00:00','SYYYY-MM-DD HH24:MI:SS'));

insert into test_part
values
(5,to_date('2008-04-28 00:00:00','SYYYY-MM-DD HH24:MI:SS'), to_date('2008-04-28 00:00:00','SYYYY-MM-DD HH24:MI:SS'));

insert into test_part
values
(6,to_date('2008-05-18 00:00:00','SYYYY-MM-DD HH24:MI:SS'), to_date('2008-05-18 00:00:00','SYYYY-MM-DD HH24:MI:SS'));

insert into test_part
values
(7,to_date('2008-05-23 00:00:00','SYYYY-MM-DD HH24:MI:SS'), to_date('2008-05-23 00:00:00','SYYYY-MM-DD HH24:MI:SS'));

insert into test_part
values
(8,to_date('2008-06-12 00:00:00','SYYYY-MM-DD HH24:MI:SS'), to_date('2008-06-12 00:00:00','SYYYY-MM-DD HH24:MI:SS'));

insert into test_part
values
(9,to_date('2008-07-17 00:00:00','SYYYY-MM-DD HH24:MI:SS'), to_date('2008-07-17 00:00:00','SYYYY-MM-DD HH24:MI:SS'));

insert into test_part
values
(10,to_date('2008-08-03 00:00:00','SYYYY-MM-DD HH24:MI:SS'), to_date('2008-08-03 00:00:00','SYYYY-MM-DD HH24:MI:SS'));


create child table with foreign key constraint:
SQL> create table test_part_sources(
visitorid       number,
sourceid        number,
timestamp       date default sysdate,
timestamp_id    number,
constraint test_part_sources_visitorid_fk foreign key (visitorid) references test_part (visitorid),
constraint test_part_sources_sourceid_fk foreign key (sourceid) references source (sourceid))
partition by range (timestamp) (
partition tps200803 values less than (TO_DATE('2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tps200804 values less than (TO_DATE('2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tps200805 values less than (TO_DATE('2008-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tps200806 values less than (TO_DATE('2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tps200807 values less than (TO_DATE('2008-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tps200808 values less than (TO_DATE('2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tps200809 values less than (TO_DATE('2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition tps200810 values less than (TO_DATE('2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition maxvalue values less than (maxvalue)
tablespace data_large);

Table created.
 


insert data into child table:
insert into test_part_sources
values
(1,2217,to_date('2008-02-23 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4437);
insert into test_part_sources
values
(1,2222,to_date('2008-02-23 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4437);
insert into test_part_sources
values
(1,2223,to_date('2008-02-23 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4437);

insert into test_part_sources
values
(2,2230,to_date('2008-03-15 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4458);

insert into test_part_sources
values
(3,2217,to_date('2008-03-23 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4466);
insert into test_part_sources
values
(3,2217,to_date('2008-05-23 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4527);

insert into test_part_sources
values
(4,2416,to_date('2008-04-25 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4499);
insert into test_part_sources
values
(4,2417,to_date('2008-06-23 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4558);

insert into test_part_sources
values
(5,2394,to_date('2008-04-28 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4502);
insert into test_part_sources
values
(5,2395,to_date('2008-09-02 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4659);

insert into test_part_sources
values
(6,2985,to_date('2008-05-18 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4522);

insert into test_part_sources
values
(7,4567,to_date('2008-05-23 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4527);
insert into test_part_sources
values
(7,4568, to_date('2008-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4566);

insert into test_part_sources
values
(8,7235,to_date('2008-06-12 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4547);

insert into test_part_sources
values
(9,8645,to_date('2008-07-17 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4582);
insert into test_part_sources
values
(9,7896,to_date('2008-09-01 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4658);

insert into test_part_sources
values
(10,6969,to_date('2008-08-03 00:00:00','SYYYY-MM-DD HH24:MI:SS'), 4599); 


data to be dropped from child table:
SQL> select * from test_part_sources partition (tps200803);

 VISITORID   SOURCEID TIMESTAMP TIMESTAMP_ID
---------- ---------- --------- ------------
         1       2217 23-FEB-08         4437
         1       2222 23-FEB-08         4437
         1       2223 23-FEB-08         4437
         2       2230 15-MAR-08         4458
         3       2217 23-MAR-08         4466


data in parent table:
SQL> select * from test_part partition (tp200803);

 VISITORID ADDEDDATE PART_DATE
---------- --------- ---------
         1 23-FEB-08 23-FEB-08
         2 15-MAR-08 15-MAR-08
         3 23-MAR-08 23-MAR-08


drop partition in child table:
SQL> alter table test_part_sources drop partition tps200803;

Table altered.


update partition key in parent table:
SQL> update test_part partition (tp200803)
set part_date = sysdate
where visitorid in (select visitorid from test_part_sources);  

1 row updated.


original parent partition with data moved:
SQL> select * from test_part partition (tp200803);

 VISITORID ADDEDDATE PART_DATE
---------- --------- ---------
         1 23-FEB-08 23-FEB-08
         2 15-MAR-08 15-MAR-08


new parent partition with data moved:
SQL> select * from test_part partition (tp200810);

 VISITORID ADDEDDATE PART_DATE
---------- --------- ---------
         3 23-MAR-08 03-OCT-08


attempt to drop now obsolete partition:
SQL> alter table test_part drop partition tp200803;
alter table test_part drop partition tp200803
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


disable foreign key constraint, drop partition, re-enable constraint:
SQL> alter table test_part_sources disable constraint test_part_sources_visitorid_fk;

Table altered.

SQL> alter table test_part drop partition tp200803;

Table altered.

SQL> alter table test_part_sources enable constraint test_part_sources_visitorid_fk;

Table altered.


Thanks.
Re: Dropped child partition retains fk constraint with parent partition after row movement [message #351951 is a reply to message #351931] Fri, 03 October 2008 16:04 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
Another dilemma I face is I would like a partitioned index on the visitorid column. However, it appears I cannot create a partitioned index on a column that is not the partition key. Dropping obsolete partitions renders my primary key index unusable. Since we anticipate this table holding a very large volume of data, rebuilding the index online could potentially take too much time/resources.
Has anyone ever worked around this before?

Thanks.
Re: Dropped child partition retains fk constraint with parent partition after row movement [message #351959 is a reply to message #351951] Fri, 03 October 2008 17:52 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The error is telling you that CONSTRAINTS exist, not that ROWS exist with referencing problems.

You are running a DDL command, not a DML command. DML commands check the rows whereas DDL commands just want the constraints to be disabled.

Oracle Error Messages Manual
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Cause: An attempt was made to truncate a table with unique or primary keys referenced by foreign keys enabled in another table. Other operations not allowed are dropping/truncating a partition of a partitioned table or an ALTER TABLE EXCHANGE PARTITION.

Action: Before performing the above operations the table, disable the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";


Ross Leishman
Previous Topic: change default database
Next Topic: merging Databases
Goto Forum:
  


Current Time: Sat Dec 03 09:50:14 CST 2016

Total time taken to generate the page: 0.09105 seconds