List:General Discussion« Previous MessageNext Message »
From:Egor Egorov Date:March 15 2004 8:58am
Subject:Re: understanding foreign keys
View as plain text  
rb <rb@stripped> wrote:
> I am a new user trying to learn mysql (using InnoDB tables, mysql
> version 3.23.54) and to create a useful database for a project I am
> working on. In trying to understand how to use foreign keys effectively,
> I studied examples at - "A Gentle Introduction to SQL"
> I am trying to understand why some columns which refer to other tables
> are defined as foreign keys and why others are not. Basically, what is
> the difference between a column which refers to another table but is not
> explicitly a "foreign key" and one which is? I would be grateful if
> anyone could help me to understand this distinction. From one of the
> sample databases, here is a create table statement which includes both
> types:
> CREATE TABLE order_line (
>  order_ref     INTEGER NOT NULL REFERENCES dress_order
> ,line_no       INTEGER NOT NULL
> ,ol_style      INTEGER REFERENCES garment
> ,ol_size       INTEGER NOT NULL
> ,ol_material   INTEGER REFERENCES material
> ,PRIMARY KEY (order_ref, line_no)
> ,FOREIGN KEY (ol_style, ol_size) REFERENCES quantities
> );
> I already discovered that in order for me to make this work, I had to
> insert a line creating indexes: INDEX (ol_style, ol_size), before the
> foreign key definition and that I had to explicitly identify the columns
> in the referenced table for the foreign keys to be created: 
> FOREIGN KEY (ol_style, ol_size) REFERENCES quantities (style_q, size_q) 
> Thanks very much for any help. sorry if I'm asking in the wrong
> place... 

In the first case (order_ref INTEGER NOT NULL REFERENCES dress_order) REFERENCES without
FOREIGN KEY is parsed, but MySQL does nothing.
In the second case REFERENCES is a part of FOREIGN KEY definition.

For technical support contracts, goto
This email is sponsored by
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   Egor.Egorov@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB /

understanding foreign keysrb15 Mar
  • Re: understanding foreign keysEgor Egorov15 Mar
    • Re: understanding foreign keysAndrew Zhu15 Mar