John Taylor-Johnston wrote:
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.
It should be a unique item rather than just a 'key' (indexed item).
If you add this data:
insert into person(name, email) values ('my name', 'my_email@xxxxxxxxxxx');
insert into person(name, email) values ('another name',
'my_email@xxxxxxxxxxx');
and I share an email address, and you use "email" as the foreign key,
which one is it going to link to?
Mysql should really throw an error if you try to reference a non-unique
field or combination of fields - every other db does.
A unique key can cover more than one field:
create table person
(
person_id int primary key,
name varchar(255),
email varchar(255),
unique key (name, email)
) engine=innodb;
So you can only have one combination of name & email then you can use
that as a foreign key:
create table shopping
(
shopping_id int primary key,
person_name varchar(255),
person_email varchar(255),
foreign key (person_name, person_email) references person(name,email)
) engine=innodb;
Though I'd suggest starting off with the person_id (primary key) as the
foreign key so you don't have data redundancy and integrity issues
(unless you use "on update cascade").
I'd also suggest getting an intro-to-sql book as this is all reasonably
basic stuff.
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
