[askdba] Re: Strange behavior

  • From: "Justin Cave (DDBC)" <jcave@xxxxxxxxxxx>
  • To: <askdba@xxxxxxxxxxxxx>
  • Date: Fri, 1 Oct 2004 17:55:27 -0600

Perhaps I am missing something obvious, but what is the point of
creating two foreign key constraints that would enforce the integrity
constraint? =20

Justin Cave  <jcave@xxxxxxxxxxx>
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]
On Behalf Of Regis Biassala
Sent: Friday, October 01, 2004 8:01 AM
To: askdba@xxxxxxxxxxxxx
Subject: [askdba] 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>=20
SQL>=20
SQL> drop table child;

Table dropped.

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

Table created.

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

-----------------------
Regis Biassala (DBA)
Direct: +353 1 8063613



Other related posts: