Forein key constraints and indexes

Hi, I have many constraints of foreign key on a table, I notice that this constraints created an implicit index, is that index slow performance when insert or update, If yes can I deactivate this index and what is consequences if I deactivate the index.

In the latest releases of Informix you can have a foreign key that is not supported by an index on the dependent table, yes. This is fine as long as you never look up the dependent table's rows using the foreign key. Under many join situations you find the dependent row using its primary key then use the foreign key value from that to find the independent table row. In this case it is fine to disable the index on the foreign key. In other applications, say an order entry system where there is a parent/child relationship, the foreign key is also part of its primary key and the lookup from the parent row uses the parent's primary key to find the child table's rows. If the parent key is at the beginning of the primary key index for the child table, then you can do without the foreign key index, though the foreign key index will sometimes be more efficient. If the primary key of the parent does not lead the child's primary key, then you will need the foreign key index for this lookup join. Foreign keys that are only used to expand codes can nearly always be used without the supporting index on the dependent table. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, May 16, 2016 at 4:25 PM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > Hi, I have many constraints of foreign key on a table, I notice that this > constraints created an implicit index, is that index slow performance when > insert or update, If yes can I deactivate this index and what is > consequences > if I deactivate the index. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c003172e3de710532fb9a3c

Hi, whats is expand codes ?

Ahh, a lookup table for say a department number. The department number is the foreign key and the only purpose of the table that it is primary key to is to expand that department number into the anem and/or location of the department. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, May 16, 2016 at 5:07 PM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > Hi, whats is expand codes ? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93b57d64b3f520532fc0f27

If the server didn't have an index, then any time an insert was done on a child table would require a sequential scan of the parent table. If it was a unique constraint, then any insert would also require a sequential scan of the table. If there was a referential constraint, then any delete of a parent row would require a sequential scan of the children tables. Madison Pruet Retired and Loving it On Monday, May 16, 2016 3:25 PM, CHALLENGER212 ABDERRAFI <abderrafi212@gmail.com> wrote: Hi, I have many constraints of foreign key on a table, I notice that this constraints created an implicit index, is that index slow performance when insert or update, If yes can I deactivate this index and what is consequences if I deactivate the index. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.