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

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sat, 18 Apr 2009 00:06:44 +0200
Message-ID: <49e8fd7b$0$201$e4fe514c_at_news.xs4all.nl>

Maxim Demenko schreef:
> 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

Back to the early Oracle days when all constraints were forced by views! But: I like this one!

Shakespeare Received on Fri Apr 17 2009 - 17:06:44 CDT