[PHP] Re: MySQL Group?
John Taylor-Johnston wrote: Does anyone know of a good MySQL group? Found it: http://lists.mysql.com/mysql/ Thanks, John -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: MySQL Group?
Hi John, John Taylor-Johnston [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Does anyone know of a good MySQL group? I want to make a relational link from `data` to `shopping` so when I insert a new record in `shopping`, I will see the contents of `data`.`name` and `data`.`email` as a drop-down menu in `shopping`. Where does one go to get this kind of help? Thanks, John DROP TABLE IF EXISTS `data`; CREATE TABLE `data` ( `id` int(5) NOT NULL auto_increment, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `data` VALUES(1, 'Allen, Carolyn', '[EMAIL PROTECTED]'); INSERT INTO `data` VALUES(2, 'Atwood, Margaret', '[EMAIL PROTECTED]'); DROP TABLE IF EXISTS `shopping`; CREATE TABLE `shopping` ( `id` int(5) NOT NULL auto_increment, `name` varchar(100) NOT NULL, `address` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ; I'm not certain but think you need to include a 'references table_name(field_name)' clause that sets up the Foreign key relationship between the 2 tables. I think the Reference clause would replace the auto_increment in your primary key of the referencing table. HTH George -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: MySQL Group?
George J wrote: Hi John, John Taylor-Johnston [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Does anyone know of a good MySQL group? I want to make a relational link from `data` to `shopping` so when I insert a new record in `shopping`, I will see the contents of `data`.`name` and `data`.`email` as a drop-down menu in `shopping`. Where does one go to get this kind of help? Thanks, John DROP TABLE IF EXISTS `data`; CREATE TABLE `data` ( `id` int(5) NOT NULL auto_increment, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `data` VALUES(1, 'Allen, Carolyn', '[EMAIL PROTECTED]'); INSERT INTO `data` VALUES(2, 'Atwood, Margaret', '[EMAIL PROTECTED]'); DROP TABLE IF EXISTS `shopping`; CREATE TABLE `shopping` ( `id` int(5) NOT NULL auto_increment, `name` varchar(100) NOT NULL, `address` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ; I'm not certain but think you need to include a 'references table_name(field_name)' clause that sets up the Foreign key relationship between the 2 tables. I think the Reference clause would replace the auto_increment in your primary key of the referencing table. The references goes into the secondary table, not the main one and it definitely doesn't replace the auto_increment field. 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 PROTECTED]'); insert into shopping(shopping_id, person_id) values(1, 1); The 'person' table still needs at least a unique field for the field being referenced (the foreign key), usually a primary key (and if necessary an auto_increment). The secondary table uses that key to check: - if that id exists: insert into shopping(shopping_id, person_id) values (2,2); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails - if it should delete that id (when using on delete cascade) mysql delete from person where person_id=1; Query OK, 1 row affected (0.00 sec) mysql select * from shopping; Empty set (0.00 sec) - if it needs to be updated (when using on update cascade) mysql update person set person_id=2 where person_id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from shopping; +-+---+ | shopping_id | person_id | +-+---+ | 1 | 2 | +-+---+ 1 row in set (0.00 sec) See manual for more examples: http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: MySQL Group?
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 PROTECTED]' ) ; INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 2, 'second Name', '[EMAIL PROTECTED]' ) ; 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 PROTECTED]'); 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
Re: [PHP] Re: MySQL Group?
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', '[EMAIL PROTECTED]');
insert into person(name, email) values ('another name',
'[EMAIL PROTECTED]');
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
