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 10 of 10

Thread: Table update

  1. #1
    New Coder
    Join Date
    Apr 2005
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Table update

    How do I relate such a way that I update a column of the main table, another secondary table's column related to the main table gets updated automatically?

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    both tables need to be created with an ENGINE=InnoDb
    you then need an index on the column on the main table and then create the foreign key on the secondary table to reference that column. You don't have to reference the primary key column on the main table, if you do then of course you don't need to create the index as there will already be one.

    Code:
    create table merchandise (
    id int auto_increment primary key,
    name varchar(25)
    ) engine=innodb
    Code:
    create table stock (
    stockid int,
    amount smallint,
     foreign key (stockid) references merchandise(id)
    ON UPDATE CASCADE 
    ON DELETE CASCADE
    ) engine=innodb
    the on update cascade says that whenever the id column in the merchandise table is updated then the stockid in the secondary table will be updated.

    the on delete cascade says that whenever the row is deleted from the merchandise table then any corresponding rows in the secondary table are also deleted where the stockid is the same as the id deleted in the main table.

  • #3
    New Coder
    Join Date
    Apr 2005
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Doesn't seems to be working.

    The StockId did not update.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    You copied the two tables above exactly including the engine=innodb?

    can you show your update?

  • #5
    New Coder
    Join Date
    Apr 2005
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, I copied exactly.
    Do you mean showing you the SQL for the update?

    Code:
    INSERT INTO merchandise (Name) VALUES ('$_POST[customersname]')

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Inserting into the merchandise table isn't going to touch the stock table. Which rows did you expect would be changed in the stock table?

  • #7
    New Coder
    Join Date
    Apr 2005
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The stockid?

  • #8
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Every StockID in the table? What would it get updated to?

  • #9
    New Coder
    Join Date
    Apr 2005
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I would expect the stockid from stock to be updated only.
    My idea for my table relation is once the id from merchandise is updated with an information, at the same time the stockid from stock which is the secondary table will be updated.

  • #10
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    and it is. but an item has to exist in stock already. you don't get a row automatically added to the stock table. it gets automatically updated or deleted and that is all.


  •  

    Posting Permissions

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