Limit same rows in junction table

2
votes

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.

2
sqloracle
just as an idea ..you can add a coulum specify if the employee is working or no. Active = Y/N - Moudiz
Just put a unique index on that UNIQUE(employeeID,EndDate) . BTW a more appropriate way to do this is using an open end date like - 31/12/2999 . - sagi
@Moudiz That column would already be in the Employee table - Tommy
@sagi unique index sounds like the way to go. I don't like using an open end date (e.g. you want to calculate the numbers of days someone works in the department) - Tommy
So you dont use the end_date, you use current date or what ever. How would a null value help you better then open date which is very far from now? Null values shouldn't be used as it can cause errors later. - sagi

2 Answers

1
votes

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.)

0
votes

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
);