Cannot drop columns that once were part of primary key
Date: Thu, 03 Jan 2013 15:54:06 +1000
Message-ID: <50E51CFE.9010703_at_tpg.com.au>
G'day.
Version is 11.2.0.3.3. I am at a loss here. In an upgrade sequence a supplier-provided script drops a foreign key, then drops a primary key, recreates it minus 3 columns, recreates the foreign key and finally drops the 3 excluded columns.
Dropping the 3 columns fails with ORA-12991: column referenced in multi-column constraint. I checked VPD policies, constraints, views, triggers: at that stage, there is absolutely no reference to any of the columns left!
Objects involved (names altered):
table drop_cols:
serialnb not null varchar2(20) operator_id not null number(3) indicator not null number(3) valid_ind not null number(10) FID not null number(10) RIA not null number(3) valid_date not null date (plus 22 more colums)
This table has 3.1 million rows of 145 bytes on average.
primary key pk_drop_cols is 6 of the 7 columns above (excluding RIA). foreign key fk_drop_cols contains only the operator_id column.
Sequence of events (the upgrade script uses a package that provides some security and prevents spurious drop errors etc.):
begin
if utils.constraint_column_exists(
'FK_THAT_REFERENCES_DROP_COL', 'OPERTAOR_ID') then
execute immediate
'alter table rtab drop constraint
FK_THAT_REFERENCES_DROP_COL';
end if;
end;
/
begin
if utils.constraint_column_exists(
'PK_DROP_COLS', 'OPERATOR_ID' ) then
execute_immediate
'alter table drop_cols drop constraint pk_drop_cols
drop index' ;
end if;
end;
/
begin
if utils.constraint_exists(
'FK_DROP_COLS') then
execute_immediate
'alter table drop_cols drop constraint fk_drop_cols';
end if;
end;
/
begin
if not utils.constraint_exists(
'PK_DROP_COLS') then
execute_immediate '
alter table drop_cols add (
constraint pk_drop_cols primary key
( serialnb, FID, valid_date)
using index tablespace &ind_ts.';
end if;
end;
/
begin
if not utils.constraint_exists(
'FK_THAT_REFERENCES_DROP_COL') then
execute_immediate '
alter table rtab add (
constraint fk_that_references_drop_col
foreign key ( serialnb, FID, valid_date )
references drop_cols ( serialnb, FID, valid_date )
';
end if;
end;
/
-- At this point, the primary key and the remote foreign key exist -- again /with the same names/ and only 3 of the original 6 columns. -- Now the 3 excluded columns are to be dropped:begin
if utils.table_column_exists(
'DROP_COLS', 'OPERATOR_ID' ) then
execute immediate '
alter table drop_cols drop (operator_id, indicator, valid_ind)
';
end if;
end;
/
..
ORA-12991: column is referenced in a multi-column constraint
The only thing I can come up with that seems to make remotely sense is a spurious recycle bin object that may retain a relationship. I did however purge the recyclebin manually and execute the 'alter table ... drop ' command without the PL/SQL wrapper, to the same result.
Any hints welcome!
Cheers,
Tony
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 03 2013 - 06:54:06 CET