Hi All,

My question is about Foreign Key and data updating.

I have tables :

1. Items
2. Invoice
3. Supplier
4. Invoice_Items

That records all the company buying history.
The records as follows :

mysql> select * from invoice;
+-----+------------+-------------+
| Id | Date | Id_Supplier |
+-----+------------+-------------+
| 100 | 2004-11-10 | 1 |
+-----+------------+-------------+

mysql> select * from items;
+----+---------+
| Id | Name |
+----+---------+
| 1 | Goods S |
| 2 | Goods B |
| 3 | Goods C |
+----+---------+

mysql> select * from supplier;
+----+------------------+
| Id | name |
+----+------------------+
| 1 | Orackel |
| 2 | Microsovt |
| 3 | Compaqks |
| 4 | San MicroSystems |
+----+------------------+

mysql> select invoice_items.Id_invoice,invoice.date as 'Date Of Transaction',supplier.name as 'Supplier Name',items.name
as 'Item Name',invoice_items.Qty
-> from invoice, supplier, items, invoice_items where
-> invoice.id = invoice_items.id_invoice and invoice_items.id_items = items.id
-> and invoice.id_supplier = supplier.id;
+------------+---------------------+---------------+-----------+------+
| Id_invoice | Date Of Transaction | Supplier Name | Item Name | Qty |
+------------+---------------------+---------------+-----------+------+
| 100 | 2004-11-10 | Orackel | Goods S | 23 |
| 100 | 2004-11-10 | Orackel | Goods B | 21 |
| 100 | 2004-11-10 | Orackel | Goods C | 2 |
+------------+---------------------+---------------+-----------+------+

Now, the last query i made depict transaction with supplier name Orackel.
Suppose someday later, i realize that i have record wrong supplier name, instead of Orackel, it should "Oravel". To fix it, i change the supplier name :

mysql> update supplier set name = 'Oravel' where id = 1;

Then the transaction is also change! (which i don't want this to happen).

mysql> select invoice_items.Id_invoice,invoice.date as 'Date Of Transaction',supplier.name as 'Supplier Name',items.name
as 'Item Name',invoice_items.Qty
-> from invoice, supplier, items, invoice_items where
-> invoice.id = invoice_items.id_invoice and invoice_items.id_items = items.id
-> and invoice.id_supplier = supplier.id;
+------------+---------------------+---------------+-----------+------+
| Id_invoice | Date Of Transaction | Supplier Name | Item Name | Qty |
+------------+---------------------+---------------+-----------+------+
| 100 | 2004-11-10 | Oravel | Goods S | 23 |
| 100 | 2004-11-10 | Oravel | Goods B | 21 |
| 100 | 2004-11-10 | Oravel | Goods C | 2 |
+------------+---------------------+---------------+-----------+------+

What should i do to solve the problem?
oh ya, this is not a coursework question or something like that.
if you dont want to answer my question then don't say anything about it like i got from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62895

no, the transaction table's supplier name will not change, provided that you actually save the transaction at the time you create it, by saving the result of the query -- this will be your history record, and you would not change any of this data once it has been saved

Thank you, r937 for the answer

The supplier name won't change ? I think it's change.
In the invoice table, Id_supplier is a Foreign key.
So if i join all the tables, Items, Invoice, Supplier, Invoice_Items.
The supplier name will be the last change i made to the supplier table.

i think this question subject is not updating but more precise is "History table".

History table shouldn't change. But, if we use foreign key on history table, we can't change the parent table.

Another example :
We have invoice_items table which record all the selling. invoice_items is a many to many relationship between invoice and items.

Suppose, someday a person buy things, we record it on invoice_items, right?
Then, later on the day we realize that we have record wrong item name but oops too bad, the items_id has gone into the invoice_items table, we can't just change it. Cause, when the person who had bought from us make a complain about the items (maybe it;s broken or something like that) we ask the person, what is the invoice number? we then use the invoice number to see what the items had been bought and need to be replace. But, the item name has change, it doesn't reflect the exact items bought by the person.