George J wrote:
Hi John,
"John Taylor-Johnston" <John.Taylor-Johnston@xxxxxxxxxxxxxxxxxxxxx> wrote in
message news:64.F3.14744.358A1E74@xxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxx');
INSERT INTO `data` VALUES(2, 'Atwood, Margaret',
'someone@xxxxxxxxxxxxxx');
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@xxxxxxxxxxx');
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
