[askdba] Re: Strange behavior

  • From: "Deepa S. Kale" <deepask@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Sun, 3 Oct 2004 09:17:16 +0530

hi regis,
first u had given table level constraint (alter table), in that what error
u got is perfect.. as table level constraint is 1-1 combo.
But next u gave column level constraint ( in column defination ,constraint
defination was given), and in such case there is almost no limit on
constraints and type of constraints on a column...just refer the create
table and alter table syntax in detail ,
-----------------------

Deepa Kale -DBA
Jyoti Structures - Nasik(IT)


                                                                           
             Regis Biassala                                                
             <Regis.Biassala@d                                             
             atalex.ie>                                                 To 
             Sent by:                  askdba@xxxxxxxxxxxxx                
             askdba-bounce@fre                                          cc 
             elists.org                                                    
                                                                   Subject 
                                       [askdba] Strange behavior           
             01-10-2004 05:31                                              
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
             askdba@freelists.                                             
                    org                                                    
                                                                           
                                                                           




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: