Results 1 to 3 of 3

Thread: Updating

  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Unanswered: Updating

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dbHobby
    mysql> update supplier set name = 'Oravel' where id = 1;

    Then the transaction is also change! (which i don't want this to happen).
    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
    Last edited by r937; 03-08-06 at 19:03.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •