Posted by Robson on 10/14/70 11:28

Hi,

I'm trying to setup a decent DB and I've just discovered the INNODB type
which is to my mind very interesting. Anyway I want to use foreign keys in
order to make easier the updating and deletion of my DB elements and in the
examples I have, I always get to declare indexes before declaring my foreign
keys. A simple example :

CREATE TABLE product (
category INT NOT NULL,
id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)
) TYPE=INNODB;

CREATE TABLE customer (
id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;

CREATE TABLE product_order (
no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)
) TYPE=INNODB;


Why do I have to declare "INDEX (product_category, product_id)" and "INDEX
(customer_id)" before each foreign key ? I have tried to create the last
table without these lines and it works fine, so what is the importance of
this kind of declaration and how does it improve my DB ?

Thanks very much for taking some of your time to read / answer my post :)

Regards
Rob

[Back to original message]