ORA-12992
Tried to drop a column from a table, but it failed with ORA-12992.
SQL> alter table customers drop column customer_id;
alter table customers drop column customer_id
*
ERROR at line 1:
ORA-12992: cannot drop parent key column
ORA-12992 means that the column you want to drop is referenced by an external foreign constraint, so you cannot drop it at this moment. In other words, the column is bound by the referential relationship.
Most likely, the column you want to drop is a primary key, you'd better keeping it for good. But, if you insist to remove it, let's see what we can do.
Solution
To solve it, we should drop all referencing constraints before dropping the column.
To find out what constraints reference to this column, we compose dropping statements by a privileged user.
SQL> show user
USER is "SYSTEM"
SQL> select 'alter table "' || owner || '"."' || table_name || '" drop constraint "' || constraint_name || '";' stmt from all_constraints where r_constraint_name in (select constraint_name from all_cons_columns where owner = 'OE' and table_name = 'CUSTOMERS' and column_name = 'CUSTOMER_ID');
STMT
--------------------------------------------------------------------------------
alter table "OE"."ORDERS" drop constraint "ORDERS_CUSTOMER_ID_FK";
alter table "SH"."SALES" drop constraint "SALES_CUSTOMER_FK";
In the above statement, we look up both ALL_CONSTRAINTS and ALL_CONS_COLUMNS to find out the real referencing constraints.
As you can see, there're 2 referencing constrains need to be dropped.
Next, we execute the statements from the result.
SQL> alter table "OE"."ORDERS" drop constraint "ORDERS_CUSTOMER_ID_FK";
Table altered.
SQL> alter table "SH"."SALES" drop constraint "SALES_CUSTOMER_FK";
Table altered.
Now, the user can proceed its action.
SQL> alter table customers drop column customer_id;
Table altered.
We solved ORA-12992.