Foreign Key Design
Posted by:
Eric
Date: March 08, 2006 04:08AM
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?