On Thu, Oct 2, 2008 at 6:50 AM, Jack van Zanen <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
>
> > 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

Reply via email to