Re: Check for no more than two entries of each value in a column?
Date: Fri, 17 Apr 2009 21:11:54 +0200
Message-ID: <49E8D47A.7080008_at_gmail.com>
livefreeordie schrieb:
> Hi,
>
> 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:
>
> PK, U1 nominator (varchar2(12))
> PK, U1 year (int)
> U1 nomination1_id (int)
> U1 nomination2_id (int)
>
> Then another table:
> PK nomination_id (int)
> nominee (varchar2(12))
> nomination_data ....
>
> However, this would force me to update any Views or Code every time
> the number of allowed nominations changes.
>
> I'd much rather do this:
>
> PK nomination_id (int)
> year (int)
> nominator (varchar2(12))
> nominee (varchar2(12))
> nomination_data ...
>
> 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?
>
> ~ Jamie
You can simulate such constraint with materialized view
(
original idea comes from Vadim Tropashko's article
http://www.dbazine.com/oracle/or-articles/tropashko8
)
SQL> create table nominations
2 (
3 nomination_id integer primary key,
4 year integer, 5 nominator varchar2(12), 6 nominee varchar2(12),
7 nomination_data varchar2(100)
8 );
Table created.
SQL>
SQL> create materialized view log
2 on nominations
3 with rowid,sequence(nominator,year)
4 including new values;
Materialized view log created.
SQL>
SQL> create materialized view nominations_mv
2 refresh fast on commit as
3 select count(*) cnt,nominator,year
4 from nominations
5 group by nominator,year;
Materialized view created.
SQL>
SQL> alter table nominations_mv
2 add constraint nominations_ck check(cnt<3);
Table altered.
SQL>
SQL> insert into nominations(
2 nomination_id,year,nominator,nominee) 3 values(1,2007,'John','Mary');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into nominations(
2 nomination_id,year,nominator,nominee) 3 values(2,2007,'John','Susann');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into nominations(
2 nomination_id,year,nominator,nominee) 3 values(3,2007,'John','Patrick');
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.NOMINATIONS_CK) violated
SQL> select nomination_id id,year,nominator,nominee
2 from nominations;
ID YEAR NOMINATOR NOMINEE ---------- ---------- ------------ ------------
1 2007 John Mary 2 2007 John Susann
Best regards
Maxim Received on Fri Apr 17 2009 - 14:11:54 CDT