On Thu, Oct 2, 2008 at 6:50 AM, Jack van Zanen <jack@xxxxxxxxxxxx> 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@xxxxxxxxx>
>
> > 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
