List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:March 6 2003 3:39am
Subject:RE: Foreign keys and being FIRST index
View as plain text  
> 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.

Thread
Foreign keys and being FIRST indexDaevid Vincent6 Mar
  • Re: Foreign keys and being FIRST indexDan Nelson6 Mar
    • RE: Foreign keys and being FIRST indexDaevid Vincent6 Mar
      • Re: Foreign keys and being FIRST indexDan Nelson6 Mar
      • RE: Foreign keys and being FIRST indexKeith C. Ivey6 Mar
        • Multiple foreign keys?Daevid Vincent8 Mar
          • re: Multiple foreign keys?Egor Egorov8 Mar
  • Re: Foreign keys and being FIRST indexWilliam R. Mussatto6 Mar