0

I have three table: Products, Stocked products, sold products.

Products and 'stocked products' are different tables because there are different batches of the same product stocked with different serial numbers. here are the important rows in those tables:

Products:

Products
ID
Designation

Stocked Products:

StockedProducts
ID
Quantity
BatchSerialNumber

Sold Products:

SoldProducts
ID
BatchSerialNumber
Quantity

BatchSerialNumber in SoldProducts references the same column in StockedProducts.

My problem is when a Stocked batch runs out, I want to delete the record of it, while keeping all the records of its sale.

How would I manage that with a foreign key?

1 Answer 1

2

My problem is when a Stocked batch runs out, I want to delete the record of it, while keeping all the records of its sale.

How would I manage that with a foreign key?

The whole purpose of a foreign key is to maintain referential integrity of the children records to their parents. It is supposed to prevent someone from removing a parent that a child is a dependent on.

If you wanted to delete a parent and not the children in such a relationship, then either the business logic is flawed or there's a database design issue. It would be nonsensical, otherwise, to do so.

It appears your StockedProduct table has a Quantity column. So, it seems like it doesn't make sense to delete a row from it, rather it should stay at Quantity 0 until it is restocked, or if it's never replenished, that's fine too. But the serialized product existed and the sale of it is dependent on that parent row.

Otherwise, if you truly wanted to delete from StockedProducts (which sounds like flawed business logic to me still), then you would need to redesign the database by removing the Foreign Key dependency from SoldProducts.

2
  • fair enough. thank you. Commented Aug 9, 2023 at 20:12
  • @AzzedineBk np, cheers!
    – J.D.
    Commented Aug 10, 2023 at 3:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.