locked
Foriegn Key with null value RRS feed

  • Question

  • hi all,

      I have table called employee which includes

    employeeID, EmployeeName, ManagerID.

    Here ManagerID is foriegn key to EmployeeID so the table is self referenced. Now one employee dont have Manger as he is the highest position. So i just put Manager ID as null.

    Is this good way of designing table? We have an argue that null in fireign key is not advisable.

    Kindly guid me to a better design.

    When i put null in Manager ID, i got foriegn key error when i delete that employee. how can i rectify this?

    Same like Is it advisable to have null in foreign key when multiple tables are relations.

    • Changed type Naomi N Wednesday, February 8, 2012 2:27 PM Question rather than discussion
    Wednesday, February 8, 2012 1:59 PM

Answers

  • It has to be logical and usable:

    A. The root of the tree (CEO) when EmpID = ManagerID

    B. The root of the tree when Manager is NULL

    Both are OK conditions in a recursive CTE.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES


    • Edited by Kalman Toth Saturday, February 11, 2012 5:57 AM
    • Marked as answer by akhilrajau Saturday, February 11, 2012 5:59 AM
    Saturday, February 11, 2012 5:57 AM
  • The problem is the record which not has Manager ID

    EG:

    Emp ID    Employee Name   Manager ID

    ------------------------------------------------

    1                Employee1        null

    This record shows the error because freign key not have any employee id. But based on my logic Employee1 is the top employee and he dont have any manger. Here confusion created so adding same employee id (1) to the manager id is one option like which is best way to handle this situation? avoiding null is best way ?

    Perfect example of "Naive Trees". Chapter 3, SQL Anti patterns, avoiding the pitfalls of database progrmming.

    akhilrajau, please read the above book. It contains a whole chapter on this topic and lists what issues developers ru into and proposes 3 different solutions.
    Possible solutions:

    • Adjacency List and using CTE's. (The one you have implemented.)
    • path enumeration.
    • Nested sets.
    • closure table

    In your case, what is the error you are facing.

    "This record shows the error because freign key not have any employee id". Couldn't get you.

    As in Bol @ http://msdn.microsoft.com/en-us/library/ms175464.aspx

    A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns.

    Why do you want to avoid null's. Have null's In a Recursive CTE, you can specify that ManagerId is null to get only managers and UNION ALL with other employees. That will build the tree for you.

    Hope this is helpful.


    Help the Community know the status of the thread.

    Please use Mark as Answer/Propose as Answer if the post solved the problem.

    Use Vote As Helpful if the post is inline or hints towards the solution.

    - Arun Kumar Allu

    • Marked as answer by akhilrajau Saturday, February 11, 2012 6:00 AM
    Saturday, February 11, 2012 5:59 AM

All replies

  • Use the same employee id as a manager id
    • Proposed as answer by Naomi N Wednesday, February 8, 2012 2:27 PM
    Wednesday, February 8, 2012 2:02 PM
  • What's the error?

    "Works for me!"

    CREATE TABLE Employee
    (
     employeeID	INT PRIMARY KEY,
     EmployeeName	VARCHAR(10),
     ManagerID	INT REFERENCES Employee
    );
    
    INSERT INTO Employee(employeeID, EmployeeName, ManagerID)
     SELECT 1, 'Moo', NULL UNION ALL
     SELECT 2, 'Cow', 1 UNION ALL
     SELECT 3, 'Bob', 1 UNION ALL
     SELECT 4, 'Bud', 3 UNION ALL
     SELECT 5, 'Boo', 4;
    
    SELECT * FROM Employee;
    1	Moo	NULL
    2	Cow	1
    3	Bob	1
    4	Bud	3
    5	Boo	4

    IMO, NULLs in an FK is an excellent idea. Filling an FK with data that is false just to satisfy some requirement of no-NULLs-in-an-FK is storing bad-data on purpose.

    • Edited by Brian Tkatch Wednesday, February 8, 2012 5:21 PM
    Wednesday, February 8, 2012 5:18 PM
  • AdventureWorks.HumanResources.Employee table has similar design.

    The orgchart of AdventureWorks Cycles company is based on that:

    http://www.sqlusa.com/bestpractices2005/organizationtree/

    Ken Sanchez, the CEO, has NULL foreign key. As suggested you can point it to itself if NULL is not desirable.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES


    • Edited by Kalman Toth Wednesday, February 8, 2012 5:26 PM
    • Proposed as answer by Brian Tkatch Wednesday, February 8, 2012 5:27 PM
    Wednesday, February 8, 2012 5:26 PM
  • hi SQLUSA,

     I used the same table you told. This is HumanResource.Employee table from Adventure works only. But my issue is when i delete a record with null as ManagerID (because that employee is higher one and not have any manager), i got Foreign key constraint error. So we have an argue that null in foreign key not good this is the problem.


    • Edited by akhilrajau Thursday, February 9, 2012 4:46 AM
    Thursday, February 9, 2012 2:08 AM
  • What was the error? Was it that there are other records that rely on this record? If so, it isn't the FK in the record causing the issue, but the FK in the other record that REFERENCES this one. As long as the record is referred to, it cannot be DELETEd. So, if you try DELETEing the top record, the TABLE better be empty, or it won't work.
    • Proposed as answer by Naomi N Friday, February 10, 2012 5:06 AM
    Thursday, February 9, 2012 1:53 PM
  • Find the below error,

     

    Exception Type : System.Data.SqlClient.SqlException

    Exception Message :
    The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_Employee_Employee_ManagerID". The conflict occurred in database "MSCS", table "HumanResources.Employee", column 'ManagerID'. The statement has been terminated.

    • Edited by akhilrajau Friday, February 10, 2012 8:59 AM
    Friday, February 10, 2012 8:57 AM
  • Check

    select count(*) from  employee where ManagerID=<id of the employee you are deleting>


    Serg

    Friday, February 10, 2012 9:18 AM
  • Hello,

    I think you are deleting an employee who is a manager of some other employee.

    select count(*) from  employee where ManagerID=<yourmanagerID>

    if the count is >0 then you need to remove that record first.

    • Proposed as answer by Naomi N Friday, February 10, 2012 4:33 PM
    Friday, February 10, 2012 11:13 AM
  • When trying to DELETE an Employee, do you want to also DELETE all that Employees that report to him?

    For a list of Employees that cannot be DELETEd (with removing their reports as well):

    SELECT
    	Manager.EmployeeID,
    	COUNT(*)
    FROM
    	Employee Manager,
    	Employee
    WHERE
    	Employee.ManagerID = Manager.EmployeeID
    GROUP BY
    	Manager.EmployeeID
    HAVING
    	COUNT(*) > 1;

    Friday, February 10, 2012 1:36 PM
  • The problem is the record which not has Manager ID

    EG:

    Emp ID    Employee Name   Manager ID

    ------------------------------------------------

    1                Employee1        null

    This record shows the error because freign key not have any employee id. But based on my logic Employee1 is the top employee and he dont have any manger. Here confusion created so adding same employee id (1) to the manager id is one option like which is best way to handle this situation? avoiding null is best way ?

    Saturday, February 11, 2012 1:58 AM
  • It has to be logical and usable:

    A. The root of the tree (CEO) when EmpID = ManagerID

    B. The root of the tree when Manager is NULL

    Both are OK conditions in a recursive CTE.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES


    • Edited by Kalman Toth Saturday, February 11, 2012 5:57 AM
    • Marked as answer by akhilrajau Saturday, February 11, 2012 5:59 AM
    Saturday, February 11, 2012 5:57 AM
  • k thanks friend
    Saturday, February 11, 2012 5:59 AM
  • The problem is the record which not has Manager ID

    EG:

    Emp ID    Employee Name   Manager ID

    ------------------------------------------------

    1                Employee1        null

    This record shows the error because freign key not have any employee id. But based on my logic Employee1 is the top employee and he dont have any manger. Here confusion created so adding same employee id (1) to the manager id is one option like which is best way to handle this situation? avoiding null is best way ?

    Perfect example of "Naive Trees". Chapter 3, SQL Anti patterns, avoiding the pitfalls of database progrmming.

    akhilrajau, please read the above book. It contains a whole chapter on this topic and lists what issues developers ru into and proposes 3 different solutions.
    Possible solutions:

    • Adjacency List and using CTE's. (The one you have implemented.)
    • path enumeration.
    • Nested sets.
    • closure table

    In your case, what is the error you are facing.

    "This record shows the error because freign key not have any employee id". Couldn't get you.

    As in Bol @ http://msdn.microsoft.com/en-us/library/ms175464.aspx

    A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns.

    Why do you want to avoid null's. Have null's In a Recursive CTE, you can specify that ManagerId is null to get only managers and UNION ALL with other employees. That will build the tree for you.

    Hope this is helpful.


    Help the Community know the status of the thread.

    Please use Mark as Answer/Propose as Answer if the post solved the problem.

    Use Vote As Helpful if the post is inline or hints towards the solution.

    - Arun Kumar Allu

    • Marked as answer by akhilrajau Saturday, February 11, 2012 6:00 AM
    Saturday, February 11, 2012 5:59 AM