2

I am new to Oracle DB and am in need of some help. I would like to drop a specific column but i am getting a specific error. The issue specifically is:

SQL Error: ORA-12991: column is reference in a multi-column constraint Cause: an attempt was made to drop a column referenced by some constraints Action: Drop all constraints referencing the dropped column or specify CASCADE CONSTRAINTS in statement.

So i have gone ahead and searched for all the constraints that referenced the column by doing something like:

SELECT * FROM USER_TAB_COLS WHERE COLUMN_NAME = 'My_Column_Name'

After finding and removing all the referenced constraints on the column that i want to drop I tried to drop the column again but i am still getting the same issue. Before posting the question i did take some time to research before this and have read the potential of this being a "Supplemental Logging" issue. Again i am very new to oracle DB programming as such could someone please shed some light on this and maybe suggest how and why i can not drop the column after having temporarily removed the constraints that reference the column i want to drop? Thanks in advance!

PS: Using Cascade Constraint Personally i don't think will fix the issue and i also do not want to remove all constraints without knowing which of them are being removed. So this is not a viable solution for me.

3

Setup:

create table t3 (c1 number, c2 number);
alter table t3 add  supplemental log group sl1 (c1, c2) always;
alter table t3 add constraint t3_uk unique (c1, c2);
insert into t3 values (1, 2);
commit;

Try to drop the column:

SQL> alter table t3 drop column c2;
alter table t3 drop column c2
                           *
ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint

So far so good, goes as expected. Drop the constraint:

SQL> alter table t3 drop constraint t3_uk;

Table altered.

SQL> alter table t3 drop column c2;
alter table t3 drop column c2
                           *
ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint

Still doesn't work, same error. Check the supplemental log group:

select * from user_log_groups where table_name = 'T3'

OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE      ALWAYS      GENERATED
----- -------------- ---------- ------------------- ----------- --------------
BP    SL1            T3         USER LOG GROUP      ALWAYS      USER NAME

Drop the supplemental log group, then try dropping the column again:

SQL> alter table t3 drop supplemental log group sl1;

Table altered.

SQL> alter table t3 drop column c2;

Table altered.
  • @Balazs-pop - I know its implied in the answer but a brief explanation that supplemental logging can cause the ORA-12991 and why would help make this a better answer. I'll up-vote anyway as I have never come across it before (after many years of Oracle use). – BriteSponge Apr 8 '16 at 8:02
  • @Balazs-pop - Thank you for the examples. I've gone ahead and accepted this answer but it has not exactly solved my problem. At least, not quite yet. I am checking the supplemental log group but am not getting no results from the table names i specified. Any reason as to why this might be the case? Thank you in advance for any suggestions. – Rex CoolCode Charles Apr 8 '16 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.