目 录
某项目impdp导入过程中报错如下
30-JAN-24 22:54:01.411: ORA-31684: Object type CONSTRAINT:"SMP"."SYS_C0056287" already exists
30-JAN-24 22:54:01.411: ORA-31684: Object type CONSTRAINT:"SMP"."SYS_C0056439" already exists
SQL> connect smp/XXXX
SQL> col owner for a10
SQL> col column_name for a20
SQL> col POSITION for a10
SQL> col TABLE_NAME for a20
SQL> SELECT A.OWNER,A.TABLE_NAME,A.COLUMN_NAME, A.POSITION FROM USER_CONS_COLUMNS A WHERE A.CONSTRAINT_NAME='SYS_C0056287';
OWNER TABLE_NAME COLUMN_NAM POSITION
---------- ---------- ---------- ----------
SMP SYS_ROLE ROLE_ID
SQL>
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','SYS_ROLE') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','SYS_ROLE')
--------------------------------------------------------------------------------
CREATE TABLE "SMP"."SYS_ROLE"
( "ROLE_ID" NVARCHAR2(32) NOT NULL ENABLE,
SQL> SELECT A.TABLE_NAME, A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE FROM USER_CONSTRAINTS A WHERE A.TABLE_NAME='SYS_ROLE';
TABLE_NAME CONSTRAINT_NAME C
---------- -------------------------------------------------------------------------------------------------------------------------------- -
SYS_ROLE SYS_C0061059 C
SYS_ROLE SYS_C0056287 C
SYS_ROLE SYS_C0083894
检查发现对应的约束确实是存在的。因此可以忽略。现场删除掉smp用户信息用,重新单独导入smp信息,问题解决。因此怀疑是前期清理数据不彻底导致。
创建主表并配置主键
create table a_tab ( id number, text varchar2(10), constraint a_tab_pk primary key (id) );
创建子表
create table b_tab (
id number,
text varchar2(10) );
新增约束
alter table b_tab add constraint b_tab_fk1 foreign key (id) references a_tab (id);
再次增加约束,提示已经存在。
alter table b_tab add constraint b_tab_fk2 foreign key (id) references a_tab (id);