Bubbling my problem down to a simple example, imagine satellite television service providers, their programming packages, their customers, and tying customers to packages. Shouldn't I be able to drop all of a provider's data in one shot?
CREATE TABLE providers
( provider_name VARCHAR2(20) NOT NULL
, CONSTRAINT providers_pk PRIMARY KEY (provider_name)
)
PARTITION BY LIST(provider_name)(PARTITION provider__dflt VALUES(DEFAULT))
/
CREATE TABLE programming
( programming_name VARCHAR2(50) NOT NULL
, provider_name VARCHAR2(20) NOT NULL
, CONSTRAINT programming_pk PRIMARY KEY (programming_name)
, CONSTRAINT programming_fk1 FOREIGN KEY (provider_name)
REFERENCES providers (provider_name)
)
PARTITION BY REFERENCE (programming_fk1)
/
CREATE TABLE subscribers
( subscriber_name VARCHAR2(50) NOT NULL
, provider_name VARCHAR2(20) NOT NULL
, CONSTRAINT subscribers_pk PRIMARY KEY (subscriber_name)
, CONSTRAINT subscribers_fk1 FOREIGN KEY (provider_name)
REFERENCES providers (provider_name)
)
PARTITION BY REFERENCE (subscribers_fk1)
/
CREATE TABLE subscriber_programming
( subscriber_name VARCHAR2(50) NOT NULL
, programming_name VARCHAR2(20) NOT NULL
, CONSTRAINT subscriber_programming_pk
PRIMARY KEY (subscriber_name, programming_name)
, CONSTRAINT subscriber_programming_fk1 FOREIGN KEY (subscriber_name)
REFERENCES subscribers (subscriber_name)
, CONSTRAINT subscriber_programming_fk2 FOREIGN KEY (programming_name)
REFERENCES programming (programming_name)
)
PARTITION BY REFERENCE (subscriber_programming_fk1)
/
ALTER TABLE providers SPLIT PARTITION provider__dflt values ('dishnetwork')
INTO (PARTITION provider_dishnetwork, PARTITION provider__dflt)
UPDATE INDEXES
/
INSERT INTO providers (provider_name)
VALUES ('dishnetwork')
/
INSERT INTO subscribers (subscriber_name, provider_name)
VALUES ('john doe', 'dishnetwork')
/
INSERT INTO programming (programming_name, provider_name)
VALUES ('top200', 'dishnetwork')
/
ALTER TABLE providers DROP PARTITION provider_dishnetwork UPDATE INDEXES
/
I don't even have to INSERT into subscriber_programming; what's above is enough to cause that last statement to pull "ORA-02266: unique/primary keys in table referenced by enabled foreign keys". The version info:
-- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
-- With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
-- Linux 2.6.32-279.11.1.el6.x86_64 #1 SMP Sat Sep 22 07:10:26 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
This simple demo is merely representative, of course. Our real tables number ~60, with hundreds of millions of records in many of the tables, spanning a FK tree several levels deep, without the partition key littered throughout. Given that data volume, we're trying hard to avoid running DELETE, which is why I'm exploring migration to PARTITION BY REFERENCE. The problem is reminiscent of the already-fixed Bug 9329773, but is slightly different - primarily in that this variant isn't fixed. If there's no way such a setup would ever fly, have you any suggestions that wouldn't involve application code changes? (For instance, adding a partition key column to child tables won't fly, but I might be able to sell disabling constraints to the developers.)