Re: Foreign Key Versus Table Index

On Thu, Oct 2, 2008 at 6:50 AM, Jack van Zanen <jack@vanzanen.com> wrote: > I am not up to scratch with innodb, but in oracle > you would have a Primary key on both the id fields in the Car and person > table and a Foreign key on PersonId linking it to Id in the Person table. > > IN your select an index on PersonId would be beneficial if the tables get > large. > > Jack > > 2008/10/2 J Hussein <jemmahussein.work@gmail.com> > > > Hi, > > > > I'm slightly confused about foriegn keys and indexes on mysql innodb > > tables. > > Foreign key constraints create a reference between two tables and indexes > > make queries on a particular table faster if the index is on a field in > the > > where or order by clause. > > > > My question was whether say for the following two tables: > > > > Person Car > > > > Id Id > > Name PersonId > > Address Make > > Phone Number Colour > > > > If I create a foriegn key linking the id field in person and the personid > > field in car, do I need to create another index in car table > specifically > > for the personid field if I was running a query such as: > > > > "SELECT Id FROM car WHERE personid={personkeynumber}"? > > > > Thanks for your help. > > > > Jemma > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > -- > J.A. van Zanen > Jenna, FKs are not to create relationships but act as constraints inside the data base. What that means is that for a record to be created that has a FK in another table, that FK'ed record MUST exist in the table before you can add that record. Ex. AN Order table and a Customer table If the Order table references the Customer table as a FK on the CustomerID field, the CustomerId record MUST exist in the table before the order table can be filled. It does not alleviate the need to have a primary key on the other table. http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.htmlfor more details -- Bastien Cat, the other other white meat