Re: Check for no more than two entries of each value in a column?
Date: Fri, 17 Apr 2009 22:00:47 +0200
Message-ID: <74s5feF152v97U1_at_mid.individual.net>
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
as
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!)
Regards
Thomas
Received on Fri Apr 17 2009 - 15:00:47 CDT