Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Sep 2006
    Thanked 0 Times in 0 Posts

    Relational Databases


    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?

  2. #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    St. Catharines, Ontario Canada
    Thanked 149 Times in 140 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.

    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts