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 http://sqlzoo.net - "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 https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ Egor.Egorov@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
