Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: Relational Databases
09-08-2006, 03:59 PM #1
- Join Date
- Sep 2006
- Thanked 0 Times in 0 Posts
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 #2
- Join Date
- Mar 2006
- St. Catharines, Ontario Canada
- Thanked 148 Times in 139 Posts
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.
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).
so now when you update the stores table by removing Sears you also delete the rows from stockonhand.Code: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;
Like I said at the beginning though, to enter the data into the second table you can't have that done automatically.Code:delete from stores where name='Sears'