Re: Re: MySQL Group?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux