View Full Version : Relational Databases

09-08-2006, 03:59 PM

i want to set up a relational database in mysql. i want one table (items) to have a field (supplier) which gathers information fromt the table (people) field (supplier). is this possible?

09-08-2006, 04:30 PM
no, you can't have it do things automatically, you still have to enter data in both tables.

what you can do is create a foreign key in the second table to prevent incorrect data being entered and also delete or update info from the second table depending on action in the first table.

two tables:

id name
1 walmart
2 target
3 sears

storeid productid quantity
1 3 22
2 5 100
3 2 9
3 4 12

now suppose that sears goes out of business and you want all their stockonhand deleted from that table. if you set your tables up correctly you can delete in table 1 and have that delete from table 2 as well.

to do this you need to use INNODB tables as MyISAM type does not enforce foreign keys (you can make them but they are not enforced).

create table stores (
id int auto_increment primary key,
name varchar(10)) engine=innodb;

create table stockonhand (
storeid int,
productid int,
quantity int,
foreign key (storeid) references stores(id) on update cascade on delete cascade
) engine=innodb;

so now when you update the stores table by removing Sears you also delete the rows from stockonhand.

delete from stores where name='Sears'

Like I said at the beginning though, to enter the data into the second table you can't have that done automatically.