Foreign Key (My- SQL Example)

Link: http://dev.mysql.com/doc/refman/5.0/en/
innodb-foreign-key-constraints.html


CREATE TABLE parent (id INT NOT NULL,
                 PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                INDEX par_ind (parent_id),
                FOREIGN KEY (parent_id) REFERENCES parent(id)
                  ON DELETE CASCADE
) ENGINE=INNODB;

A more complex example in which a product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in the customer table:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                  price DECIMAL,
                  PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                   PRIMARY KEY (id)) ENGINE=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)) ENGINE=INNODB;