ORA-12991: column is referenced in a multi-column constraint
ORA-12991 means that the column you want to drop is bound to some multi-column constraints. You must drop the constraint first. The question is which constraint should be dropped?
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
This is really a simple drop column statement.
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 off 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 single-column constraint, we don't have to touch it.