Primary Key question

[lance59]

I have an inventory db with a table called items which I am using GUID for the item_id and table_key (auto incrementing) for the primary key. Works just fine. I also have a table called item_history which is a copy of items table including the primary key field. I am using this to be able to recall the items history. Just before I make a change to the item in items table it copies it to the item_history table. If another change is required it again copies (inserts not update) it to item_history.

The problem I have is when I copy the row from items to item_history the second time I get an error about duplicate primary key if I have the primary key turned on in item_history.

My question is does the item_history table need a primary key? Or how do I get around this?

TIA

 

[jbenson001]

I really don't see a need for a PK in the history table. But, if you want to add one, create a new column, and don't use the PK column from the original table as the pk column because you will get an error as you have seen.

 

[markros]

Item history should have its own PK, not by the item. You should not get this error if the Item_history table is designed correctly.

You may post that table script in case something is wrong.

 

[hmckillop]

Yes, it needs a seperate primary key, and a foreign key relationship to the items table.
All tables should have a PK to uniquely identify the row where potentially no other unique key exists.

If you take for example
day 1 your item row has a stock level of 4
Entry in item_history itm1, stocklevel 4

Day 2 you change this and insert a row with the change (not sure if you insert after change or before)
e.g. item_history has a second row for itm1 with stocklevel of 2

Day 3 you then change the stocklevel again and insert another entry to item_history with itm1 and stocklevel 4

Now this is just an example, but if how would you uniquely identify this - i know you will likely have a date changed field or something similiar but its just for making a point.

Create your PK as an identity, and create a FK for items PK which it references.



"I'm living so far beyond my income that we may almost be said to be living apart