Thanks for getting me started. (Sorry, I'm a top quoter.) Below is some
working code for the archives.
What I've learned so far is that :
1) what I'm referring to in `person` has to be a key.
2) if I want to refer to more than one field from person in shopping, I
have to use unique keys.
I'm still discovering what keys are for.
John
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
`person_id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`email` varchar(255) default NULL,
PRIMARY KEY (`person_id`),
KEY `email` (`email`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 1, 'Name',
'email@xxxxxxxxxxx' ) ;
INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 2, 'second
Name', 'email@xxxxxxxxxxxxxxxxxx' ) ;
DROP TABLE IF EXISTS `shopping`;
CREATE TABLE IF NOT EXISTS `shopping` (
`shopping_id` int(11) NOT NULL,
`email` varchar(255) default NULL,
`name` varchar(255) default NULL,
PRIMARY KEY (`shopping_id`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `name` (`name`),
FOREIGN KEY (`email`) REFERENCES `person` (`email`),
FOREIGN KEY (`name`) REFERENCES `person` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
You end up with something like this:
create table person
(
person_id int primary key,
name varchar(255),
email varchar(255)
) engine=innodb;
create table shopping
(
shopping_id int primary key,
person_id int,
foreign key (person_id) references person(person_id)
) engine=innodb;
insert into person(person_id,name,email)
values(1,'Name','email@xxxxxxxxxxx');
insert into shopping(shopping_id, person_id) values(1, 1);
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
