November 17, 2014 at 5:21 am
Hi All,
I have two tables. Main table name is Department which has below mentioned columns
DeptId int PK
DeptName nvarchar(50)
isActive bit
dtCreated datetime
dtModified datetime
Another is Userdetails table with following columns
Userid nvarchar(20)
Userfname nvarchar(50)
Userlname nvarchar(50)
DOB datetime
Deptid int FK
isActive bit
dtCreated datetime
dtModified datetime
Department is the main table and Deptid is the FK in UserDetails table. My issue is, i am giving an option for user to delete Department but for User, they are only made as Inactive. Because i require UserDetails table data for some reference.
But if i try to delete Department, it gives me error as there is a reference of DeptId in UserDetails table.
I do not want to set any default value in UserDetails table on DeptId column if i have to delete Department.
Please let me know how can i resolve this issue.
thanks in advance.
November 17, 2014 at 6:32 am
If you have a foreign key defined on a column you cannot leave a value in the column that is not in the referenced table. Here are your options:
1. Set the foreign key to ON DELETE SET NULL. This means that when you delete a row from the referenced table the referencing table will be updated to have a NULL in the deptid column. This does mean you must set the deptid column to allow NULL. This link explains how to do options 1 and 2, http://technet.microsoft.com/en-us/library/ms186973(v=SQL.105).aspx
2. Create a dummy row in the department table and set that value as the default in the userdetails table and set the foreign key to ON DELETE SET DEFAULT. You said you didn't want to do this, but, in my opinion, it is an option in this case.
3. Usually a foreign key is enforcing a business rule, in this case it would be a user must be assigned to a department. If this is the rule, then you'd need a 2-step process to delete a department. Assign any users in that department to their new department and then delete the department.
4. Implement a "soft" delete in the department table. This means have an active flag or deleted flag on the table and instead of actually doing a delete just set the flag marking it as deleted/inactive. This way your foreign key is not violated. You do have to change the application code to handle both the update instead of delete and to only display active/non-deleted departments when assigning departments to users.
I can't think of any other options, but there may be others that I haven't thought of.
Edit: added link.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 17, 2014 at 8:13 am
Hi Jack,
Thanks for the proposed solution. I have opted the solution of doing a soft delete on department table so that even if the user is made inactive, i would always have the option to view which department the user is associated with.
Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply