ORA-02273: this unique/primary key is referenced by some foreign keys

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