rb <[EMAIL PROTECTED]> 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 / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
