Sorry.....

It's a Foreign Key constraint




Regards

Colin

There are 10 types of people in the world, those that understand binary and
those that don't





>From: "Obnoxio The Clown" <obnoxio@serendipita.com>
>To: "Colin Dawson" <cjd_1955@hotmail.com>
>CC: informix-list@iiug.org
>Subject: Re: Add Constraint
>Date: Wed, 31 Aug 2005 11:03:08 +0100 (BST)
>
>
>Colin Dawson said:
> >
> > I've just added a constraint on a table both the master and detail
>tables
> > have over 100M rows, and it took almost 2 hours, does anyone know what
> > exactly happens during an ADD CONSTRAINT.
>
>What kind of constraint?
>
>--
>
>Bye now,
>Obnoxio
>
>"C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule"
> - Coluche
>
>A smile is a gift that is free to the giver and precious to the recipient.
>But giving someone the finger is free too, and I find it more personal and
>sincere.
sending to informix-list

Then it is checking for every detail table whether the key exists in the master table.

Just a discussion out of curiosity: Only informix chose to implement PKY->FKY relationship
via an index. In Oracle, Db2, and SQL Server, FKY does not need an index.

There are both plus and minus of Informix approach. The plus is that the join of master
and child will always use index. In other dbs, if the DBA forgets to create it, it will
suffer in performance.

The minus is that, sometimes for small code tables, where there is no need of index,
Informix will still create an index. On a large table, this is a waste of space.


"Colin Dawson" <cjd_1955@hotmail.com> wrote in message
news:1125493955.f768b65ae648bec8f0ae7f0d8366cf63@t eranews...
>
> Sorry.....
>
> It's a Foreign Key constraint
>
>
>
>
> Regards
>
> Colin
>
> There are 10 types of people in the world, those that understand binary and
> those that don't
>
>
>
>
>
>>From: "Obnoxio The Clown" <obnoxio@serendipita.com>
>>To: "Colin Dawson" <cjd_1955@hotmail.com>
>>CC: informix-list@iiug.org
>>Subject: Re: Add Constraint
>>Date: Wed, 31 Aug 2005 11:03:08 +0100 (BST)
>>
>>
>>Colin Dawson said:
>> >
>> > I've just added a constraint on a table both the master and detail
>>tables
>> > have over 100M rows, and it took almost 2 hours, does anyone know what
>> > exactly happens during an ADD CONSTRAINT.
>>
>>What kind of constraint?
>>
>>--
>>
>>Bye now,
>>Obnoxio
>>
>>"C'est pas parce qu'on n'a rien ` dire qu'il faut fermer sa gueule"
>> - Coluche
>>
>>A smile is a gift that is free to the giver and precious to the recipient.
>>But giving someone the finger is free too, and I find it more personal and
>>sincere.
> sending to informix-list