CODEWITHSUNDEEP
sql-serverforeign-keysconstraints
JD1
JD1

Reputation: 27

sqlserver foreign key constraint on lookup table

Looking for the correct design to handle this situation (this example isn't real, just displaying the concept of my question):

Let's say you have tblStates that is used as a lookup table controlling what states can be selected in tblEmployees (tblEmployees.state). There is a foreign key constraint on tblEmployee so you can only select states existing in tblStates.

I want the end-user to be able to delete states from tblStates, but this is restricted if tblEmployees has a record with the same state targeted for deletion. I want to allow the end user to delete the state in tblStates and have it remain in existing rows of tblEmployees. It should only restrict the deleted state when users make updates going forward.

Upvotes: 0

Views: 408

Answers (3)

Lee
Lee

Reputation: 189

What you seem to be describing is the ability to use or not use a state, depending on if it is available and not 'deleted'; delete I think is not a physical operation in this instance. For the tblStates table, why don't you create an instead of trigger and then if someone deletes a state, simply set a flag 'deleted' in the trigger, so someone else coming along can't use that state. Referential Integrity will still be valid since the state exists in tblStates.

Upvotes: 0

Oxians
Oxians

Reputation: 82

The way such a scenario is usually handled is by marking the row in tblStates as inactive, but not deleting it from the table.

To do this, you would add a boolean column (IsDeleted for example), and set it when you no longer want the state to be visible to the user for new rows in tblEmployees.

This preserves the integrity of your data, and enables you to still work with the tblEmployees table in a same way, regardless of whether the associated tblStates row was deleted or not (by Joining the tables for example)

Upvotes: 0

Rahul
Rahul

Reputation: 77876

Considering that you want to keep the FOREIGN KEY constraint in place. In that case, don't actually delete the record from tblStates table on end user deletion; rather have a separate column in tblEmployees saying State_Status and update that column with DELETED status probably (OR) a have a BIT column say IsStateDeleted and set it to TRUE.

Upvotes: 1

Related Questions