...

View Full Version : Table update



ooiyh
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?
:confused:

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

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

The StockId did not update.

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

can you show your update?

ooiyh
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]')

Fumigator
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?

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

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

ooiyh
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.

guelphdad
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum