Tommy February 2016

Limit same rows in junction table

I have a table (oracle database) where i store departments and their employees.

Table: DepartmentEmployee

╔══════════════╦═══════════╦═════════════╦═══════════════════════════╗
║    Column    ║ Data type ║ Nullability ║        Description        ║
╠══════════════╬═══════════╬═════════════╬═══════════════════════════╣
║ DepartmentID ║ INT       ║ NOT NULL    ║ Foreign Key to Department ║
║ EmployeeID   ║ INT       ║ NOT NULL    ║ Foreign Key to Employee   ║
║ StartDate    ║ DATE      ║ NOT NULL    ║ Date the employee joined  ║
║ EndDate      ║ DATE      ║ NULL        ║ Date the employee left    ║
╚══════════════╩═══════════╩═════════════╩═══════════════════════════╝

An employee should only have one (1) department he is currently working at. The department he is working would have no EndDate (= NULL).

So the query would be:

SELECT * FROM DepartmentEmployee WHERE EmployeeID = 1 AND EndDate IS NULL;

When an employee is no longer working at the company he would have no (0) departments with an EndDate.

So my question is: How can i make sure that only 1 or 0 rows in the table have an EndDate set to NULL for an EmployeeID? Thanks.

Answers


Zbynek Vyskovsky - kvr000 February 2016

You can add unique index ensuring that there is at most one record per employeedId and endDate:

create unique index EmployeeEndDate on DepartmentEmployee
(
     employeeId,
     endDate
);


Thorsten Kettner February 2016

Here is one option: Create a unique index on records without end date. That is a functional index containing CASE WHEN.

create unique index idx_OnlyOneNoEndDate on DepartmentEmployee
(
  case when EndDate is null then EmployeeID end
);

When EndDate is filled both expressions are null, and Oracle doesn't create index entries on null values by default, so no entry gets written. If however EndDate is null, then we write an entry for the EmployeeID. When a second record for an employee with EndDate NULL gets inserted, then the uniqueness of the index gets violeted and we get an exception.

This also means that you must first write the EndDate for an existing record and only then insert the new one. If you did it vice versa, you'd get the exception. If this is a problem for you then you should be able to circumvent this by creating a virtual column on case when EndDate is null then EmployeeID end instead and create a deferrable unique index on this column. (Deferrable indexes must reference columns only. They cannot be functional indexes unfortunately.)

Post Status

Asked in February 2016
Viewed 2,195 times
Voted 7
Answered 2 times

Search




Leave an answer