关于ORA-02273错误，以前还真没有仔细留意过。昨天遇到了这个问题，遂顺便总结一番，以后遇到这类问题就可以直接用下面方案解决。如下所示，我们首先准备一下测试环境。
CREATE TABLE TEST.TEST
( OWNER VARCHAR2(30),
OBJECT_ID NUMBER,
OBJECT_NAME VARCHAR2(30)
);
CREATE INDEX TEST.IX_TEST_N1 ON TEST.TEST(OBJECT_ID) TABLESPACE TEST_DATA;
ALTER TABLE TEST.TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (OBJECT_ID) USING INDEX TABLESPACE TEST_DATA;
CREATE TABLE TEST.RF_TEST
(
ID NUMBER,
OBJECT_ID NUMBER
);
ALTER TABLE TEST.RF_TEST ADD CONSTRAINT PK_RF_TEST PRIMARY KEY(ID) USING INDEX TABLESPACE TEST_DATA;
ALTER TABLE TEST.RF_TEST ADD CONSTRAINT FK_RF_TEST FOREIGN KEY(OBJECT_ID ) REFERENCES TEST.TEST(OBJECT_ID);
如下所示，由于脚本上面的事务，导致TEST.TEST的主键约束对应的索引为IX_TEST_N1。
SELECT OWNER
,CONSTRAINT_NAME
,CONSTRAINT_TYPE
,TABLE_NAME
,INDEX_NAME
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME='TEST';
此时假如我们要调整表TEST.TEST的主键，那么可以用下面脚本查看一下TEST表的主外键约束关系。如下所示：
SELECT DC.OWNER AS "PARENT_TABLE_OWNER",
DC.TABLE_NAME AS "PARENT_TABLE_NAME",
DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME",
DC.STATUS AS "PRIMARY CONSTRAINT STATUS",
DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME",
DF.STATUS AS "FOREIGN CONSTRAINT STATUS",
DF.STATUS AS "CHILD_TABLE_OWNER",
DF.TABLE_NAME AS "CHILD_TABLE_NAME" ,
'ALTER TABLE ' || DF.OWNER || '.' || DF.TABLE_NAME || ' DISABLE CONSTRAINT ' || DF.CONSTRAINT_NAME || ';'
FROM DBA_CONSTRAINTS DC,
(SELECT C.OWNER,
C.CONSTRAINT_NAME,
C.R_CONSTRAINT_NAME,
C.TABLE_NAME,
C.STATUS
FROM DBA_CONSTRAINTS C
WHERE CONSTRAINT_TYPE = 'R') DF
WHERE DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAME
AND DC.OWNER =UPPER('&OWNER')
AND DC.TABLE_NAME=UPPER('&TABLE_NAME');
删除表的主键约束时，报如下错误：“ORA-02273: this unique/primary key is referenced by some foreign keys”
SQL> ALTER TABLE TEST.TEST DROP CONSTRAINT PK_TEST;
ALTER TABLE TEST.TEST DROP CONSTRAINT PK_TEST
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
我们用最上面脚本生成的禁用外键约束的脚本，禁用外键约束后，然后删除表TEST.TEST的主键约束，依然报ORA-02273错误。
如上所示，不能通过先禁用外键约束，然后删除主键约束的这样操作，搜索了相关资料后，发现只能先删除外键约束，然后才能处理主键约束。
操作步骤如下:
1: 首先生成外键约束的创建脚本，后续删除外键约束后，需要重新创建外键约束。
ORACLE 11g或以上版本使用下面脚本：
--此脚本适用于Oracle 11g
SELECT 'ALTER TABLE ' || T1_OWNER || '.' || T1_TABLE_NAME
|| ' ADD CONSTRAINT ' || T1_CONSTRAINT_NAME
|| ' FOREIGN KEY (' || T1_COLUMN_NAMES || ')'
|| ' REFERENCES ' || T2_OWNER || '.' || T2_TABLE_NAME
|| '(' || T2_COLUMN_NAMES || ');' FK_SCRIPT
FROM
(SELECT A.OWNER T1_OWNER
, A.TABLE_NAME T1_TABLE_NAME
, A.CONSTRAINT_NAME T1_CONSTRAINT_NAME
, B.R_CONSTRAINT_NAME T2_CONSTRAINT_NAME
-- CONCATENATE COLUMNS TO HANDLE COMPOSITE
-- FOREIGN KEYS
, LISTAGG(A.COLUMN_NAME,', ')
WITHIN GROUP (ORDER BY A.POSITION)
AS T1_COLUMN_NAMES
FROM DBA_CONS_COLUMNS A
, DBA_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'R'
GROUP BY A.OWNER
, A.TABLE_NAME
, A.CONSTRAINT_NAME
, B.R_CONSTRAINT_NAME
) T1,
(SELECT A.OWNER T2_OWNER
, A.TABLE_NAME T2_TABLE_NAME
, A.CONSTRAINT_NAME T2_CONSTRAINT_NAME
-- CONCATENATE COLUMNS FOR PK/UK REFERENCED
-- FROM A COMPOSITE FOREIGN KEY
, LISTAGG(A.COLUMN_NAME,', ')
WITHIN GROUP (ORDER BY A.POSITION)
AS T2_COLUMN_NAMES
FROM DBA_CONS_COLUMNS A
, DBA_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE IN ( 'P', 'U' )
GROUP BY A.OWNER
, A.TABLE_NAME
, A.CONSTRAINT_NAME ) T2
WHERE T1.T2_CONSTRAINT_NAME = T2.T2_CONSTRAINT_NAME
AND T1.T1_OWNER = T2.T2_OWNER
AND T2.T2_OWNER ='&OWNER'
AND T2.T2_TABLE_NAME = '&TABLE_NAME';
ORACLE 11g之前版本使用下面脚本
--此脚本适用于Oracle 10g
SELECT 'ALTER TABLE ' || T1_OWNER || '.' || T1_TABLE_NAME
|| ' ADD CONSTRAINT ' || T1_CONSTRAINT_NAME
|| ' FOREIGN KEY (' || T1_COLUMN_NAMES || ')'
|| ' REFERENCES ' || T2_OWNER || '.' || T2_TABLE_NAME
|| '(' || T2_COLUMN_NAMES || ');' FK_SCRIPT
FROM
(SELECT A.OWNER T1_OWNER
, A.TABLE_NAME T1_TABLE_NAME
, A.CONSTRAINT_NAME T1_CONSTRAINT_NAME
, B.R_CONSTRAINT_NAME T2_CONSTRAINT_NAME
-- CONCATENATE COLUMNS TO HANDLE COMPOSITE
-- FOREIGN KEYS [HANDLES UP TO 5 COLUMNS]
, MAX(DECODE(A.POSITION, 1,
A.COLUMN_NAME,NULL)) ||
MAX(DECODE(A.POSITION, 2,', '||
A.COLUMN_NAME,NULL)) ||
MAX(DECODE(A.POSITION, 3,', '||
A.COLUMN_NAME,NULL)) ||
MAX(DECODE(A.POSITION, 4,', '||
A.COLUMN_NAME,NULL)) ||
MAX(DECODE(A.POSITION, 5,', '||
A.COLUMN_NAME,NULL))
T1_COLUMN_NAMES
FROM DBA_CONS_COLUMNS A
, DBA_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'R'
GROUP BY A.OWNER
, A.TABLE_NAME
, A.CONSTRAINT_NAME
, B.R_CONSTRAINT_NAME
) T1,
(SELECT A.OWNER T2_OWNER
, A.CONSTRAINT_NAME T2_CONSTRAINT_NAME
, A.TABLE_NAME T2_TABLE_NAME
-- CONCATENATE COLUMNS FOR PK/UK REFERENCED
-- FROM A COMPOSITE FOREIGN KEY
, MAX(DECODE(A.POSITION, 1,
A.COLUMN_NAME,NULL)) ||
MAX(DECODE(A.POSITION, 2,', '||
A.COLUMN_NAME,NULL)) ||
MAX(DECODE(A.POSITION, 3,', '||
A.COLUMN_NAME,NULL)) ||
MAX(DECODE(A.POSITION, 4,', '||
A.COLUMN_NAME,NULL)) ||
MAX(DECODE(A.POSITION, 5,', '||
A.COLUMN_NAME,NULL))
T2_COLUMN_NAMES
FROM DBA_CONS_COLUMNS A, DBA_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE IN ( 'P', 'U' )
GROUP BY A.OWNER
, A.TABLE_NAME
, A.CONSTRAINT_NAME ) T2
WHERE T1.T1_OWNER = T2.T2_OWNER
AND T1.T2_CONSTRAINT_NAME = T2.T2_CONSTRAINT_NAME
AND T2.T2_OWNER ='&OWNER'
AND T2.T2_TABLE_NAME = '&TABLE_NAME';
使用上面脚本生成的脚本为
ALTER TABLE TEST.RF_TEST ADD CONSTRAINT FK_RF_TEST FOREIGN KEY (OBJECT_ID) REFERENCES TEST.TEST(OBJECT_ID);
2：生成删除外键约束的脚本
SELECT 'ALTER TABLE '
|| OWNER || '.' || TABLE_NAME
|| ' DROP CONSTRAINT '
|| CONSTRAINT_NAME
||';' CONSTRAINT_DISABLE
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R'
AND STATUS = 'ENABLED'
AND R_CONSTRAINT_NAME IN
(
SELECT CONSTRAINT_NAME
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_TYPE IN ('P', 'U')
AND OWNER='&OWNER'
AND TABLE_NAME = '&TABLE_NAME'
);
执行上面脚本生成的脚本，删除外键约束。
SQL> ALTER TABLE TEST.RF_TEST DROP CONSTRAINT FK_RF_TEST;
Table altered.
3：删除表TEST.TEST的主键
SQL> ALTER TABLE TEST.TEST DROP CONSTRAINT PK_TEST;
Table altered.
SQL> SELECT OWNER
2 ,TABLE_NAME
3 ,INDEX_NAME
4 FROM DBA_INDEXES
5 WHERE TABLE_NAME='TEST';
OWNER TABLE_NAME INDEX_NAME
---------- ------------------------------ ------------------------------
TEST TEST IX_TEST_N1
SQL>
如下所示，这种情况下，删掉了约束，并不会删除对应的索引。所以必须手工删除该索引
4： 在表TEST.TEST上增加主键约束， 在表TEST.RF_TEST上添加外键约束。
SQL> ALTER TABLE TEST.TEST ADD CONSTRAINT PK_TEST PRIMARY KEY(OBJECT_ID) USING INDEX TABLESPACE TEST_DATA;
Table altered.
SQL> ALTER TABLE TEST.RF_TEST ADD CONSTRAINT FK_RF_TEST FOREIGN KEY (OBJECT_ID) REFERENCES TEST.TEST(OBJECT_ID);
Table altered.
SQL>