Change foreign key

[ClaraBarton]

I have a table of locations. These locations are permanent and never removed.
I have inventory in those locations that often needs to be moved to another location.
Inventory has a foreign key tied to the primary key in locations.
If I want to move Inventory to a different location, all I need to do is change the foreign key.
With referential integrity I can't delete the foreign key. Or change it.
How would this be done?

 

[theDBguy]

Don't you just update the values of those foreign keys?

 

[ebs17]

Wrong approach. There should be a many to many relationship between location and inventory. Then a move is simply a new record in the junction table, with timestamp.

 

[ClaraBarton]

Even though there is only one inventory item to each location?
And then there would be no foreign key in Inventory?

 

[ClaraBarton]

Even though there is only one inventory item to each location?
And then there would be no foreign key in Inventory?

Pat Hartman: I'm having a weird problem with the forum software so I can't post there. There is no problem at all with replacing a FK with a new FK value. It just has to be a valid value. Also if the FK is not required, it could be null but I don't think that makes sense in this case. If you want to continue, please copy this reply into the forum for me so everyone can see it and I'll add more if you need it.

 

[ClaraBarton]

The FK isn't required, but key restraints will not let me remove it.

 

[The_Doc_Man]

There should be a distinction between updating an FK and emptying an FK. An FK, if marked as required, cannot be erased - but should be possible to change.

On the other hand, ebs17 is not technically wrong that a junction table should be a reasonable approach. But it would only apply if you are going to need to remember any history of when that inventory was moved from location to location. Do you have a requirement for inventory history?

 

[ClaraBarton]

No, probably not. But I do want to empty the FK and then refill it, not do it all at once. The more I think about it, the more I like the junction table. It makes sense. Thank you all for your input.

 

[Josef P.]

The FK isn't required, but key restraints will not let me remove it.

How do you try to remove them? Update with a value or with Null?

But I do want to empty the FK and then refill it, not do it all at once.

What is the advantage of this approach?

 

[Gasman]

Surely if the inventory FK has to be null, then all the items have been processed/dispatched/sold?, so the record would be deleted?
If you move from one location to another, I would have thought that that FK would just be amended to a new location that exists?

As mentioned what you have now seems fine (to me at least) as long as you do not want to see a history of where inventory gets moved from/to?

 

[Pat Hartman]

No, probably not. But I do want to empty the FK and then refill it, not do it all at once.

Did you remove the 0 default? Is the FK defined as required? There is absolutely no problem with having a Null FK value provided you have not defined the FK as required AND have not retained the zero value as the default. Zero is NOT a valid value so you cannot change the FK to zero. You cannot change it to "" either since "" is a Zero Length String which is not numeric.

As I said, you can do it but it makes no sense because inventory always has to be somewhere.

I'm back. Something really weird was happening to my forum connection. It kept thinking I was not logged in and then it wouldn't log in but if I managed to navigate to a thread where I had postested earlier, everything was fine.

 

[June7]

If you don't care about history of inventory locations, sounds like just need to change location ID saved in Inventory table. If referential integrity won't allow this, something is wrong with relationship. Could you provide db for analysis?

What do you mean by 'inventory'? Inventory tracking is a dynamic process - items coming in and going out. Transaction records document this traffic. Calculate inventory balances when needed. Review http://allenbrowne.com/AppInventory.html