Reputation: 1520

Dealing with circular reference when entering data in SQL

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?

Upvotes: 8

Answers (9)

spencer7593

Reputation: 108410

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

[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]

Upvotes: 5

Cade Roux

Reputation: 89671

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.

Upvotes: 1

Erich Kitzmueller

Reputation: 36987

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)

Upvotes: 3

Patrick McDonald

Reputation: 65421

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

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

Create the Employee (EmployeeA) in the 'Unassigned' Department
Create the new department (DepartmentA) with the employee EmployeeA
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

Upvotes: 1

chaos

Reputation: 124297

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.

Upvotes: 13

HLGEM

Reputation: 96570

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.

Upvotes: 0

Bob

Reputation: 99744

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.

Upvotes: 1