DROP COLUMN ORA-12991 & ORA-12983

 Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as reporttest

SQL> SELECT * FROM P1;

USERNAME                 USERCODE   VALIDSTATUS
-------------------              ------------------  -----------
ORACLE                         10000001      1

SQL>  SELECT I.TABLE_NAME, I.INDEX_NAME, I.COLUMN_NAME
  2     FROM USER_IND_COLUMNS I, USER_CONSTRAINTS C
  3    WHERE C.TABLE_NAME = 'P1'
  4      AND I.TABLE_NAME = C.TABLE_NAME
  5      AND C.CONSTRAINT_TYPE = 'P'
  6  ;

TABLE INDEX_NAME                     COLUMN_NAME
----- ------------------------------ --------------
P1    PK_P1                          USERCODE
P1    PK_P1                          VALIDSTATUS

VALIDSTATUS与USERCODE是P1表的复合主键。

 

--1.被删除的列不能是复合主键的

SQL> ALTER TABLE P1 DROP COLUMN VALIDSTATUS;

ALTER TABLE P1 DROP COLUMN VALIDSTATUS

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

SQL> ALTER TABLE P1 DROP COLUMN VALIDSTATUS  CASCADE;

ALTER TABLE P1 DROP COLUMN VALIDSTATUS  CASCADE

ORA-02000: missing CONSTRAINTS keyword

--如果想删除包含复合主键的列，要使用CASCADE  CONSTRAINTS，同时主键被删除。
SQL> ALTER TABLE P1 DROP COLUMN VALIDSTATUS  CASCADE  CONSTRAINTS;

Table altered

-- 2.与表中是否有记录无关，表中有记录也可以删除列
SQL> SELECT * FROM P1;

USERNAME                       USERCODE
------------------------------      ----------
ORACLE                            10000001

SQL> ALTER TABLE P1 DROP COLUMN USERCODE;

Table altered

--3.当表中只剩一个列时，这个列不能被删除
SQL> ALTER TABLE P1 DROP COLUMN USERNAME;
ALTER TABLE P1 DROP COLUMN USERNAME
ORA-12983: cannot drop all columns in a table

SQL>