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?

Centime

This seems like a strange schema to me, can you explain why you have the relationships this way? – Poundal

Is EmployeeId in your department table like a Department manager or something? – Leukemia

In Oracle, the "trick" is to define the foreign key(s) DEFERRABLE. – Sulphate

Answer 1

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)

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 AskTom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10954765239682

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

[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. Note that disabling the constraint will affect all sessions, so while the constraint is disabled, other sessions could potentially insert and update rows which will cause the reenable of the constraint to fail.

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]

Sulphate

Just found out that Sybase will let you do it too with the SET OPTION WAIT_FOR_COMMIT = 'ON'. Setting it to ON will delay the referential check until you call COMMIT. SET OPTION WAIT_FOR_COMMIT = 'ON' ; insert into Employees... insert into Departments... COMMIT; SET OPTION WAIT_FOR_COMMIT = 'OFF' ; Now are only missing MSSQL ;) – Centime

-1 "a better approach is to remove the NOT NULL constraint" -- how do you prevent the "temporary placeholder" from remaining indefinitely. – Idiophone

@Tom: thanks for the helpful note, about enabling "deferred constraint checking" on Sybase. – Sulphate

Answer 2

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.

Joseph

No reason you can't have a foreign key on a nullable field. – Musquash

Chaos: Certainly that is one option. Bill: The idea was that each Department must have a department head. – Centime

Also Employees.DepartmentID could be made nullable instead. it's like "Chicken or the egg". But the schema still smells. – Potshot

Answer 3

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)

Vibraculum

Sounds like the approach described by Spencer7593. Anybody knows how to that in MSSQL? – Centime

Answer 4

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.

Fireball

if so how to relate the head of department to the employee data ? – Exemplify

adapting this way we may need another table to break down the relation with applying unique constrain for both columns all together – Exemplify

Answer 5

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.

Poundal

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

Hammurabi

Answer 7

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.

Lakitalaks

Answer 8

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

Giusto

Answer 9

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.

Thach

I don't why that is not a viable design structure. The employee belongs to a department, a department has to have a manager (department head). – Centime

No a department does not have to have a manager. Department head positions get vacant. But it is not viable because you cannot ever have a circular pk/fk situation and make it work with hacking to get around the constraints. The need to hack to get around the constraints is your first clue that the design is bad. I woul dhavea an employees table which is the parent table, an organization table that includes the organizational structure and then an assignments table that has the structure the person is assigned to and the hierarchy (who hereports to). Which has the FK to both. – Thach