add foreign key in mysql


foreign key constratints

Innodb supports foreing keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. The syntax for an Innodb foreign key constraint definition in the CREATE TABLE or ALTER TABLE statement looks like this:

Syntax :
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ….)
REFERENCES tbl_name (index_col_name, ………)
[ON DELETE reference_option]
[ON UPDATE reference_option]

Optoin :
RESTRICT | CASCADE | SET NULL | NO ACTION

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)

alter table android_device add contraint foreign key (User_ID) Rerferences android_master (User_ID)  on delete

cascade on update cascade

alter table androi_device add constraint foreign key(field_name) references android_master (fieldname) on

delete cascase on update cascade

For more information :

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s