Preventing Indexes on Foreign Keys

In article <6f3pql$ji6$1@news.xmission.com>, Marco Greco <marcog@linux.ctonline.it> writes > >I wish there was! I have a couple of tables with 6 or 7 FK's with a pretty low >cardinality (one has only 12 different values over some 200K rows). Just imagine >the overhead of inserting new rows, had I actually declared the columns as FK's! > I'd probably only declare them as foriegn keys in a small development database.. of course you'll need to test your apps pretty throughly. >Ciao, >Marco >_______________________________________________________________________________ >Marco Greco, Catania, Italy marcog@linux.ctonline.it >rem radioterapia +39 95 447828 fax 446558 > >Informix faq http://www.iiug.org/techinfo/faq/informix.htm >4glworks http://www.ctonline.it/~marcog >Informix on Linux http://www.ctonline.it/~marcog/ifmxlinux.htm > >David Kosenko wrote: >> >> LT J Koermer <jkoermer@LSU.USCG.mil> offerred: >> :Hi everyone, >> :I have several tables that I create with Foreign Keys on columns, but I >> :do NOT want Informix to automatically put an index on some of these >> :columns. Is there a way to prevent this? >> >> No, there is not. If you have a FK defined on a column, and index must be >> present. Note that you can create your own index BEFORE defining the FK >> constraint, and Informix will use your index if it is the same as what it >would >> create for the FK. >> >> As to why this must be, remember that a FK defines a relationship between two >> tables that must be enforced. A table with a FK cannot have a row inserted >into >> it with a value for that FK that does not exist in the referenced table. The >> index on the primary key (PK) is needed to do a quick lookup to verify this. >> Going in the other direction, the referenced table cannot have a row deleted >> when the primary key value is contained in a table referencing that primary >> key. A need to test this condition quickly must be available, so an index on >> the FK is essential. Without it, whenever you wanted to delete a row from a >> table with a PK, it would have to do a SEQUENTIAL SEARCH on every table with a >> FK referencing this table to determine if any rows contained that PK value. >> Think of the performance hit there! -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care