sql - Limit same rows in junction table -

i have table (oracle database) store departments , employees.

table: departmentemployee

╔══════════════╦═══════════╦═════════════╦═══════════════════════════╗ ║    column    ║ data type ║ nullability ║        description        ║ ╠══════════════╬═══════════╬═════════════╬═══════════════════════════╣ ║ departmentid ║ int       ║ not null    ║ foreign key department ║ ║ employeeid   ║ int       ║ not null    ║ foreign key employee   ║ ║ startdate    ║ date      ║ not null    ║ date employee joined  ║ ║ enddate      ║ date      ║ null        ║ date employee left    ║ ╚══════════════╩═══════════╩═════════════╩═══════════════════════════╝ 

an employee should have 1 (1) department working at. department working have no enddate (= null).

so query be:

select * departmentemployee employeeid = 1 , enddate null;

when employee no longer working @ company have no (0) departments enddate.

so question is: how can make sure 1 or 0 rows in table have enddate set null employeeid? thanks.

here 1 option: create unique index on records without end date. functional index containing case when.

create unique index idx_onlyonenoenddate on departmentemployee (   case when enddate null employeeid end ); 

when enddate filled both expressions null, , oracle doesn't create index entries on null values default, no entry gets written. if enddate null, write entry employeeid. when second record employee enddate null gets inserted, uniqueness of index gets violeted , exception.

this means must first write enddate existing record , then insert new one. if did vice versa, you'd exception. if problem should able circumvent creating virtual column on case when enddate null employeeid end instead , create deferrable unique index on column. (deferrable indexes must reference columns only. cannot functional indexes unfortunately.)