关于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>