Post reply

Deleting the foreign key reference

  • November 17, 2014 at 5:21 am

    #301131

    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

    #1758960

    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

    #1758995

    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