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

From: Maxim Demenko <mdemenko_at_gmail.com>
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