[askdba] Re: Strange behavior

  • From: Todd Pepling <tpepling@xxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Fri, 01 Oct 2004 16:41:29 -0400

Did you verify both FKs exist via DBA_CONSTRAINTS/DBA_CONS_COLUMNS?
-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]On
Behalf Of Regis Biassala
Sent: Friday, October 01, 2004 9:03 AM
To: Regis Biassala; askdba@xxxxxxxxxxxxx
Subject: [askdba] Re: Strange behavior

This does not happen with DB2...

-----Original Message-----
From: Regis Biassala 
Sent: 01 October 2004 12:59
To: 'askdba@xxxxxxxxxxxxx'
Subject: Strange behavior

I've tested this against: oracle8i,9i,10g and MSSQL 2000, it is behaving
exactly the same...
I came across this while debugging an installation scripts...
See test below:

SQL> create table parent (id int not null primary key);

Table created.

SQL> create table child(parentid int);

Table created.

SQL> alter table child add constraint fk_constraint_1 foreign key(parentid)
  2  references parent(id);

Table altered.

SQL> alter table child add constraint fk_constraint_2 foreign key(parentid)
  2  references parent(id);
alter table child add constraint fk_constraint_2 foreign key(parentid)
ERROR at line 1:
ORA-02275: such a referential constraint already exists in the table

SQL> drop table child;

Table dropped.

SQL> create table child
  2  (parentid int,
  3  constraint fk_constraint_1  foreign key(parentid) references
  4  constraint fk_constraint_2 foreign key(parentid) references

Table created.

I wanted to share this with you, so be aware of this behavior.

Regis Biassala (DBA)
Direct: +353 1 8063613

-- Attached file included as plaintext by Ecartis --
-- Desc: Signature

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any

Other related posts: