I think, the two formats you raised are both that of foreign key constraint:
when there is a composite foreign key (more than 1 attribute in a table involved), you must use "foreign key" clause. if there is only a simple foreign key, you can use either format. --- Egor Egorov <[EMAIL PROTECTED]> wrote: > 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] > __________________________________ Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
