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>