View Full Version : Table update

08-25-2006, 12:25 PM
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?

08-25-2006, 05:18 PM
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.

create table merchandise (
id int auto_increment primary key,
name varchar(25)
) engine=innodb

create table stock (
stockid int,
amount smallint,
foreign key (stockid) references merchandise(id)
) 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.

08-28-2006, 06:40 PM
Doesn't seems to be working.

The StockId did not update.

08-28-2006, 07:25 PM
You copied the two tables above exactly including the engine=innodb?

can you show your update?

08-29-2006, 03:55 AM
Yes, I copied exactly.
Do you mean showing you the SQL for the update?

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

08-29-2006, 04:42 AM
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?

08-29-2006, 06:18 AM
The stockid?

08-29-2006, 06:54 AM
Every StockID in the table? What would it get updated to?

08-29-2006, 07:12 AM
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.

08-29-2006, 01:55 PM
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.