Foreign key

[JanuszOZ]

Hi,

Can some one tell my how do I define Foreign key in mysql when creating table eg:
create table blah (
first_filed char(1),
second_field char(1),
Prime key (first_field),
<============= how do I tell mysql that second key are Foreign key

 

[QatQat]

First of all you have to convert all tables involved to InnoDB, then index the keys you want to referenciate to, then create the foreign key.
In this example you also enable cascade delete if the referenced record is deleted.


example, two tables, parent and children


CREATE TABLE parent(
id INT unsigned NOT NULL auto_increment,
parent_name varchar (40),
PRIMARY KEY (id),
index par_ind (id))
TYPE=INNODB;


CREATE TABLE child(
id INT unsigned not null auto_increment,
parent_id INT unsigned,
child_name varchar (40),
index child_ind (id),
Primary key (id),
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
)
TYPE=INNODB;

If you have existing tables,

alter table table_name TYPE=InnoDB


Bye

QatQat

Life is what happens when you are making other plans.