Hello and welcome to our community! Is this your first visit?
Register
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
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relational Databases

    hi,

    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?
    thanks
    michael

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 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:

    stores
    id name
    1 walmart
    2 target
    3 sears

    stockonhand
    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).

    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;
    so now when you update the stores table by removing Sears you also delete the rows from stockonhand.

    Code:
    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
    •