ORA-02275: such a referential constraint already exists in the table

SQL>  create table test1(id number, name varchar2(30));

 Table created.

 SQL> create table test2(id number, age number);

 Table created.

 SQL> alter table test1 add constraint pk_test1 primary key(id);

 Table altered.

 SQL> alter table test2 add(constraint fk_test2_test1 foreign key(id) references test1(id));

 Table altered.

 SQL> alter table test2 add(constraint fk2_test2_test1 foreign key(id) references test1(id));

alter table test2 add(constraint fk2_test2_test1 foreign key(id) references test1(id))

 *

ERROR at line 1:

ORA-02275: such a referential constraint already exists in the table

 

SELECT c.owner,

       a.table_name,

       a.column_name,

       a.constraint_name,

       C.R_CONSTRAINT_NAME     

FROM ALL_CONS_COLUMNS A, ALL_CONSTRAINTS C 

where A.CONSTRAINT_NAME = C.CONSTRAINT_NAME

  and C.CONSTRAINT_TYPE = 'R'

  and C.TABLE_NAME='TEST2';

 

OWNER

TABLE_NAME

COLUMN_NAME

CONSTRAINT_NAME

R_CONSTRAINT_NAME

SCOTT

TEST2

ID

FK_TEST2_TEST1

PK_TEST1

 So you can see the referential constraint already exists. 

Comments