Re: Re: MySQL Group?

George J wrote:

Hi John, "John Taylor-Johnston" <John.Taylor-Johnston@cegepsherbrooke.qc.ca> wrote in message news:64.F3.14744.358A1E74@pb1.pair.com...

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', 'nobody@jtjohnston.net'); INSERT INTO data VALUES(2, 'Atwood, Margaret', 'someone@jtjohnston.net'); 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@address.com'); 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/