[askdba] Re: Strange behavior

  • From: Rajesh Puneyani <rajpuneyani@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Fri, 1 Oct 2004 14:54:07 -0400

Hi Regis,

Thanks for this information.
But why would you need to create 2 FKs going against the same column
of same table.

So looks like error msg from ALTER TABLE is valid but success of
CREATE TABLE is confusing.

Regards
Rajesh


On Fri, 1 Oct 2004 13:01:25 +0100, Regis Biassala
<regis.biassala@xxxxxxxxxx> wrote:
> 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>
> SQL>
> 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: