Re: Check for no more than two entries of each value in a column?

From: Thomas Kellerer <>
Date: Fri, 17 Apr 2009 22:00:47 +0200
Message-ID: <>

livefreeordie wrote on 17.04.2009 19:38:
> I need to create an app where any given employee may nominate up to
> TWO other employees per year for an award. This number could change.
> Now I could create a table like this:
> And have some kind of Check Constraint to only allow two nominees per
> nominator per year. But I don't know how to do that.
> Thoughts?

You can do this with a constraint on a materialized view:

create table nominations

    nomination_id integer not null,
    year integer not null,
    nominator varchar2(12) not null,
    nominee varchar2(12) not null,
    constraint pk_nominations primary key (nomination_id) );

create materialized view log on
nominations with rowid ( year, nominator) including new values;

create materialized view nominee_count
refresh fast on commit
select year, nominator, count(*) nominator_year_count from nominations
group by year, nominator

alter table nominee_count
add constraint check_nomination_count check(nominator_year_count <= 2);

Now when you try to insert a third nomination per nominator and year you will get an error when committing the data (not during insert!)

(This has been shamelessly stolen from a presentation by Tom Kyte, so the credits go to him!)

Thomas Received on Fri Apr 17 2009 - 15:00:47 CDT

Original text of this message