Dealing with circular reference when entering data in SQL

Question

What kind of sql tricks you use to enter data into two tables with a circular reference in between.

Employees
    EmployeeID <PK>
    DepartmentID <FK> NOT NULL

Departments
    DepartmentID <PK>
    EmployeeID <FK> NOT NULL

The employee belongs to a department, a department has to have a manager (department head).

Do I have to disable constraints for the insert to happen?

Answer 1

Yes, in this instance you will have to disable a foreign key.

Answer 2

Refactor the schema by removing the circular reference.
Delete an ID column from either of the table schema.

Departments.EmployeeID doesn't seem to belong there in my opinion.

Answer 3

I can't think of a non hackish way to do this. I think you will need to remove the constraint or do some type of silly dummy values that get updated after all the inserts.

I'd recommend refactoring the DB schema. I can't think of any reasons why you would want it to work this way.

Maybe something like, Employee, EmployeeDepartment (EmployeeId, DepartmentId) and Department would be a better way to accomplish the same goal.

Answer 4

You need to get rid of one or the other reference permanently . This is not a viable design structure. Which has to be entered first? Department or Employee? Unless your departments are all one employee big, the structure doesn't make sense anyway as each employee would have to have a distinct departmentid.

Answer 5

I assume your Departments.EmployeeID is a department head. What I'd do is make that column nullable; then you can create the department first, then the employee.

Answer 6

You could create a row in the Department table for 'Unassigned'

To create a new department with a new Employee you then would

1. Create the Employee (EmployeeA) in the 'Unassigned' Department
2. Create the new department (DepartmentA) with the employee EmployeeA
3. Update EmployeeA to be in DepartmentA

This wouldn't invalidate your current schema, and you could set up a task to be run regularly to check there are no members of the Unassigned department.

You would also need to create a default employee to be the Employee of Unassigned

EDIT:

The solution proposed by chaos is much simpler though

Answer 7

This problem could be solved with deferable constraints. Such constraints are checked when the whole transaction is commited, thus allowing you to insert both employee and department in the same transaction, referring to each other. (Assuming the data model makes sense)

Answer 8

There are a few good designs I've used. All involve removing the "manager" EmployeeID from the Department table and removing the DepartmentID from the Employee table. I've seen a couple answers which mention it, but I'll clarify how we used it:

I typically end up with an EmployeeDepartment relationship link table - many to many, usually with flags like IsManager, IsPrimaryManager, IsAdmin, IsBackupManager etc., which clarify the relationship Some may be constrained so that there is only one Primary Manager allowed per department (although a person can be a PrimaryManager of multiple departments). If you don't like the single table, then you can have multiple tables: EmployeeDepartment, ManagerDepartment, etc. but then you could have situations where a person is a manager but not an employee, etc.

We also typically allowed people to be members of multiple departments.

For simplified access, you can provide views which perform the join appropriately.

Answer 9

Q: Do I have to disable constraints for the insert to happen?
A: In Oracle, no, not if the foreign key constraints are DEFERRABLE (see example below)

[EDIT]

A: In Microsoft SQL Server, you can't defer foreign key constraints like you can in Oracle. Disabling and re-enabling the foreign key constraint is an approach, but I shudder at the prospect of 1) performance impact (the foreign key constraint being checked for the ENTIRE table when the constraint is re-enabled), 2) handling the exception if (when?) the re-enable of the constraint fails.

With SQL Server, a better approach is to remove the NOT NULL constraint, and allow for a NULL as temporary placeholder while rows are being inserted/updated.

For SQL Server:

    -- (with NOT NULL constraint removed from Departments.EmployeeID)
    insert into Departments values ('foo',NULL)
    go
    insert into Employees values ('bar','foo')
    go
    update Departments set EmployeeID = 'bar' where DepartmentID = 'foo'
    go

[/EDIT]

For Oracle:

    SET CONSTRAINTS ALL DEFERRED;
    INSERT INTO Departments values ('foo','dummy');
    INSERT INTO Employees values ('bar','foo');
    UPDATE Departments SET EmployeeID = 'bar' WHERE DepartmentID = 'foo';
    COMMIT;

Let's unpack that:

• (autocommit must be off)
• defer enforcement of the foreign key constraint
• insert a row to Department table with a "dummy" value for the FK column
• insert a row to Employee table with FK reference to Department
• replace "dummy" value in Department FK with real reference
• re-enable enforcement of the constraints

NOTES: disabling a foreign key constraint takes effect for ALL sessions, DEFERRING a constraint is at a transaction level (as in the example), or at the session level (ALTER SESSION SET CONSTRAINTS=DEFERRED;)

Oracle has allowed for foreign key constraints to be defined as DEFERRABLE for at least a decade. I define all foreign key constraints (as a matter of course) to be DEFERRABLE INITIALLY IMMEDIATE. That keeps the default behavior as everyone expects, but allows for manipulation without requiring foreign keys to be disabled.

see AskTom: http://www.oracle.com/technology/oramag/oracle/03-nov/o63asktom.html

see also: http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_12.shtml