ORA-12991: column is referenced in a multi-column constraint

  • by

ORA-12991

ORA-12991 means that the column you want to drop is bound to some multi-column constraint. Usually, it's a primary key constraint, but not always.

SQL> alter table hr.job_history drop column start_date;
alter table hr.job_history drop column start_date
                                       *
ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint

Let's see what constraints are involved with the column. Here we leverage my previous post: How to Check Primary Key of a Table.

SQL> column CONSTRAINT_NAME format a30;
SQL> column POSITION format 99;
SQL> column COLUMN_NAME format a30;
SQL> select a.constraint_name, b.position, b.column_name, c.constraint_type from all_cons_columns a left join all_cons_columns b on a.constraint_name = b.constraint_name inner join all_constraints c on a.constraint_name = c.constraint_name where a.owner = 'HR' and a.table_name = 'JOB_HISTORY' and a.column_name = 'START_DATE' order by 1,2;

CONSTRAINT_NAME                POSITION COLUMN_NAME                    C
------------------------------ -------- ------------------------------ -
JHIST_DATE_INTERVAL                     START_DATE                     C
JHIST_DATE_INTERVAL                     END_DATE                       C
JHIST_EMP_ID_ST_DATE_PK               1 EMPLOYEE_ID                    P
JHIST_EMP_ID_ST_DATE_PK               2 START_DATE                     P
JHIST_START_DATE_NN                     START_DATE                     C

As you can see, the column is part of some constraints. The first multi-column constraint is a check, the other is the primary key.

Solution

Since the column is bound to multi-column constraints, we can't drop the column directly. Instead, we should lift both of these constraints first, then drop the column.

SQL> alter table hr.job_history drop constraint JHIST_DATE_INTERVAL;

Table altered.

SQL> alter table hr.job_history drop constraint JHIST_EMP_ID_ST_DATE_PK;

Table altered.

Now, we can actually drop the column.

SQL> alter table hr.job_history drop column start_date;

Table altered.

The column has been dropped.

Please note that, NOT NULL constraint is not a multi-column constraint, we don't have to touch it.

Leave a Reply

Your email address will not be published. Required fields are marked *