Can I Have A Constraint On Count Of Distinct Values In A Column In Sql?

Table: Relatives emp_id dep_id(composite primary key) We have to restrict one employee to three dependents.

Solution 1:

This cannot be done using a check constraint alone, but there is a way using a materialized view and a check constraint as I demonstrate here on my blog. For your example this would be:

create materialized view emp_dep_mv
build immediate
refresh complete oncommitasselect emp_id, count(*) cnt
from relatives
groupby emp_id;

altertable emp_dep_mv
addconstraint emp_dep_mv_chk
check (cnt <=3)
deferrable;

However, this approach might not be performant in a large, busy production database, in which case you could go for an approach that uses triggers and a check constraint, plus an extra column on the employees table:

altertable employees add num_relatives number(1,0) default0notnull;

-- Populate for existing dataupdate employees
set num_relatives = (selectcount(*) from relatives r
                     where r.emp_id = e.emp_id)
whereexists (select*from relatives r
              where r.emp_id = e.emp_id);

altertable employees addconstraint emp_relatives_chk
check (num_relatives <=3);

createtrigger relatives_trg
after insertorupdateordeleteon relatives
foreachrowbegin
   if inserting or updating thenupdate employees
      set    num_relatives = num_relatives +1where  emp_id = :new.emp_id;
   end if;
   if deleting or updating thenupdate employees
      set    num_relatives = num_relatives -1where  emp_id = :old.emp_id;
   end if;
end;

Solution 2:

Add an new integer not null column occurrence, add a check constraint occurrence BETWEEN 1 AND 3, add a unique constraint on the compound of emp_id and occurrence, optionally add helper procs to maintain occurrence values.