MySQL Forums
Forum List  »  PHP

Re: Help in MySQL
Posted by: Roland Bouman
Date: August 12, 2005 11:56AM

I recommend you read this part of the manual:

http://dev.mysql.com/doc/mysql/en/ansi-diff-foreign-keys.html
http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html
http://dev.mysql.com/doc/mysql/en/example-foreign-keys.html
http://dev.mysql.com/doc/mysql/en/alter-table.html
http://dev.mysql.com/doc/mysql/en/create-table.html

but here's a quick start

create table a (
id int unsigned not null primary key
, ...any other columns
) engine = InnoDB
;

create table b (
ref_a int unsigned
) engine = InnoDB
;

alter table b
add constraint fk_b_a
foreign key (
ref_a
) references a (
id
)
on delete cascade
;

so,
1) both referenced and referencing tables need to be InnoDB tables
2) the referenced table needs to have a primary key or unique constraint
3) the referencing table need to have columns that can refer to the columns in the referenced table's primary key or unique constraint. The datatypes of these columns should be identical, but they need nod have the same name.
4) you have to create a foreign key constraint on the referencing table.

The tables can be in different databases, but only as long as these reside in the same MySQL server instance. If your tables are not innodb tables, the synta is parsed but not enforced, so beware of that.

Options: ReplyQuote


Subject
Written By
Posted
August 11, 2005 10:38PM
Re: Help in MySQL
August 12, 2005 11:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.