How can I enforce referential integrity for optionally present uniquekeys?

Question

We have multiple offices, and within each office there are multiple departments (some departments have employees in multiple offices). We have two existing systems that identify employees in different ways: in one, employees are identified by IDA; in the other employees are identified by IDB.

In the cases where an employee is identified by an IDA, we need to identify that employee's supervisor, if any, in SUPERVISOR_IDA.

My table looks like this:

IDA
IDB
SUPERVISOR_IDA
OFFICE
DEPARTMENT

Employees could have positions in more than one office, or in more than one department, so the same IDA or IDB could exist more than once, but with a different office, department or both.

The problem is that IDA could be null (and if so, then IDB is not null) or IDB could be null (and if so, then IDA is not null), or both could be present.

I'm trying to set up unique and/or primary keys and constraints to ensure the integrity of the database.

So I created a unique key on (IDA, IDB, OFFICE, DEPARTMENT).

Here's my problem:

I need to ensure that employees reference their supervisors. I wanted to have a self-referencing foreign key so that removal of supervisors would not leave orphan employee records who have a non-null SUPERVISOR_IDA. But since I was required to include IDB in my unique key on this table, if I create this foreign key I am required to include IDB as such:

local PARENT_IDA -> reference IDA
local OFFICE -> reference OFFICE
local DEPARTMENT -> reference DEPARTMENT
**local IDB -> reference IDB**

This is a problem because the employee IDB should NOT be the same as the supervisor IDB.

I know it seems like I'm trying to do too many things in one table perhaps, but in reality my domain is quite difficult to describe and so I created the employee/office/department as an example to illustrate my problems. I really cannot split IDA and IDB into separate tables, as they are intertwined in some problematic ways and the presence of one, the other, or both, has some important meaning that cannot be separated.

At first I wanted to set up a unique key on (IDA, OFFICE, DEPARTMENT) in addition to the aforementioned unique key, but unlike with unique keys that consist of a single column, composite unique keys will treat (null, 'A') and (null, 'A') as duplicates instead of allowing the null column to avoid violation of the uniqueness constraint.

Answer 1

I am not sure if this works in Oracle but in SQL Server I would create a trigger that on the SUPERVISORS table that fires on UPDATE and DELETE. The trigger would query the EMPLOYEES Table for any records where SUPERVISORS.SUPERVISOR_IDA = EMPLOYEES.SUPERVISOR_IDA. If any records where found, it would roll back the transaction.

I found this link outlines what you need to do.

http://www.techonthenet.com/oracle/triggers/before_delete.php

Answer 2

I think the problem is with the model. The table should have a primary key (and if IDA or IDB can be null then they are not PK columns) and the foreign key should reference the PK.

I think you are trying to use an FK against a unique index to enforce a bunch of cross-row validation rules in the data model, such as "an employee can only be supervised by someone in the same office and department" and "an IDA employee can only be supervised by another IDA employee".

In practice those are very hard to enforce when you consider multiple people potentially updating different columns on different rows at the same time.

That said, you could try adding columns DEPT_IDA and OFFICE_IDA and using triggers to set them from DEPT and OFFICE only when IDA is set. Then create the UK on those columns

Answer 3

I think your data model is wrong. You should have only one EMPLOYEE record per employee. Then you can have unique keys on each of IDA and IDB.

Because employees work at multiple offices then you need a table to represent that; POSTS would be an intersection table between OFFICES and EMPLOYEES.

The point being that SUPERVISOR_IDA and SUPERVISOR_IDB are properties of POSTS, and as such you can enforce a foreign key between those columns and the EMPLOYEES table. Use check constraints to ensure that if the POSTS record is identified by an EMPLOYEE_IDA the SUPERVISOR_IDA is populated and ditto for EMPLOYEE_IDB.