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?

Options: ReplyQuote


Subject
Written By
Posted
Foreign Key Design
March 08, 2006 04:08AM
March 08, 2006 04:23AM
March 08, 2006 02:06PM
March 08, 2006 08:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.