[askdba] Re: Strange behavior

  • From: "Anthony Molinaro" <amolinaro@xxxxxxxx>
  • To: <askdba@xxxxxxxxxxxxx>
  • Date: Fri, 1 Oct 2004 20:22:27 -0400

I think he was just trying to point out the odd behavior, that's all.

 - a

-----Original Message-----
From: Justin Cave (DDBC) [mailto:jcave@xxxxxxxxxxx]=20
Sent: Friday, October 01, 2004 7:55 PM
To: askdba@xxxxxxxxxxxxx
Subject: [askdba] Re: Strange behavior


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

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>=3D20
SQL>=3D20
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: