> In the last episode (Mar 05), Daevid Vincent said: > > http://www.mysql.com/doc/en/SEC457.html states that "there > must be an > > index where the foreign key and the referenced key are listed as the > > FIRST columns." Will this restriction be lifted soon? It is > > incredibly frustrating. I don't see why they have to be indexes, and > > more importantly, I don't see why they have to be FIRST! Ugh. > > I'm not sure that sentence means what you think it does. What they're > saying is you need to index both fields, and if you decide to > make that index a compound one with multiple keyparts, the foreign/referenced > field must be the first. They don't have to be the first > fields in the table or anything.
Right, but sometimes it isn't, or you already used up that 'first' spot for a different foreign key reference in another table. Maybe I am still not understanding something elementary, but I've tried to create some tables, and certain ones work, while others don't, and they're always related to that foreign key issue. In any event, it shouldn't matter if it's first or not! I should be able to use any/all fields in a any number of databases as foreign keys reguardless of their position in the schema or index. > As for why you want them indexed. Have you ever tried deleting a lot > of records from a table with a foreign key constraint on another table > with no index? Each delete of your first table requires a full table > scan of the second table, to make sure you're not violating the > constraint. A co-worker forgot to index a constraint in Oracle once > and his table updates took 2 hours instead of 2 minutes. I don't dispute the benefit of indexes, I just don't think it should be a REQUIREMENT. And to be honest, sometimes a table isn't made of millions of records, maybe it's only a few, but you still want ref integrity. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
