I was just looking for a second (third,fourth...) opinion on database implementation. What I have is a table which has a relationship with another table.
Let's say items, with items sold. I would like to be able to delete the items at will (if my inventory changes) but would like to keep a permanent record in 'items sold'. I would like to have a relationship with the tables, and have some control using restraints. What might be the best way to implement the allow of deletion of certain items, but the disallowment of the creation of an item being created in the 'items sold' that is not in the 'items' table?
What would be the best way to implement this, or is there a better design solution?
1. Table ITEMS is the parent table of ITEMS_SOLD.
2. You would like to be able to delete from ITEMS without deleting child rows in ITEMS_SOLD
3. Whenever inserting into ITEMS_SOLD, you want to validate the ITEMS/ITEMS_SOLD relationship.
First of all, a personal opinion : why somebody would like to be able to delete parent keys while child rows exist is beyond my comprehension.
As a solution to your problem, I suggest you validate your relationship using a trigger that fires after insert of ITEMS_SOLD.
The reason for this is that the database may have discontinued items, and they may have to be deleted from the database. I know it is not the best approach in theory, where there would be no orphans in a database, but I just wanted to find out the best way to retain information in databases that are constantly changing.