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  |
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  |
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 |
rleishman
Messages: 3728
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
|
|
|
|
Goto Forum:
Current Time: Fri Nov 29 17:52:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
