Re: relationship and tables.

[Markus Popp]

You can do it like this:

CREATE TABLE `table_a` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(5) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) TYPE=InnoDB;

CREATE TABLE `table_b` (
`id` int(11) NOT NULL auto_increment,
`id_a` int(11) NOT NULL,
`number` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `id_a` (`id_a`)
) TYPE=InnoDB;

ALTER TABLE `table_b`
ADD CONSTRAINT `table_b_ibfk_1` FOREIGN KEY (`id_a`) REFERENCES `table_a` (`id`);

Take care that you have to use InnoDB tables to make this work.

This works for all relation types, but you should never make a n:n relation. Instead you should split it into 2 (or more, if necessary) tables with 1:n relation.

--
Markus Popp, Web Developer, mysql.com
Oracle Austria