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

栏目: 数据库 · Oracle · 发布时间: 4年前

内容简介:关于ORA-02273错误,以前还真没有仔细留意过。昨天遇到了这个问题,遂顺便总结一番,以后遇到这类问题就可以直接用下面方案解决。如下所示,我们首先准备一下测试环境。如下所示,由于脚本上面的事务,导致TEST.TEST的主键约束对应的索引为IX_TEST_N1。

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

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

此时假如我们要调整表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错误。

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

如上所示,不能 通过先禁用外键约束,然后删除主键约束的这样操作,搜索了相关资料后,发现只能先删除外键约束,然后才能处理主键约束。

操作步骤如下:

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> 

如下所示,这种情况下,删掉了约束,并不会删除对应的索引。所以必须手工删除该索引

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

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> 

以上所述就是小编给大家介绍的《ORA-02273: this unique/primary key is referenced by some foreign keys》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

敏捷软件开发

敏捷软件开发

马丁 / 邓辉、孙鸣 / 人民邮电出版社 / 2008-01-01 / 69.00元

《敏捷软件开发:原则模式和实践(C#版)》不仅是一部深入浅出、生动易懂的面向对象原则与设计模式著作。而且还是一部通俗的敏捷方法导引书和快速实用的LJML教程。通过《敏捷软件开发:原则模式和实践(C#版)》你会发现,许多以前看起来非常枯燥费解的概念,忽然间都豁然开朗。变得鲜活生动起来。 C#版与此前的Java版相比,主要的更新包括加强了UML的介绍章节。使其更加贴近实战;增加了对MVP模式的介......一起来看看 《敏捷软件开发》 这本书的介绍吧!

Base64 编码/解码
Base64 编码/解码

Base64 编码/解码

XML 在线格式化
XML 在线格式化

在线 XML 格式化压缩工具

HEX HSV 转换工具
HEX HSV 转换工具

HEX HSV 互换工具