Is it possible to limit the number of different values ​​in an SQL column?

Question

Table: Relatives

• emp_id
• dep_id (composite primary key)

We must limit one employee to three dependents.

Answer 1

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

 create materialized view emp_dep_mv build immediate refresh complete on commit as select emp_id, count(*) cnt from relatives group by emp_id; alter table emp_dep_mv add constraint emp_dep_mv_chk check (cnt <= 3) deferrable; 

However, this approach can be inefficient in a large busy production database, in which case you could use an approach that uses triggers and a control constraint, plus an additional column in the employees table:

 alter table employees add num_relatives number(1,0) default 0 not null; -- Populate for existing data update employees set num_relatives = (select count(*) from relatives r where r.emp_id = e.emp_id) where exists (select * from relatives r where r.emp_id = e.emp_id); alter table employees add constraint emp_relatives_chk check (num_relatives <= 3); create trigger relatives_trg after insert or update or delete on relatives for each row begin if inserting or updating then update employees set num_relatives = num_relatives + 1 where emp_id = :new.emp_id; end if; if deleting or updating then update employees set num_relatives = num_relatives - 1 where emp_id = :old.emp_id; end if; end; 

Answer 2

Add a new integer, not the occurrence null column, add the occurrence control constraint occurrence BETWEEN 1 AND 3 , add a unique constraint for the emp_id and occurrence join, optionally add helper routines to maintain the occurrence values.