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:05:26 +0200
Message-ID: <49e8fd2d$0$201$e4fe514c_at_news.xs4all.nl>



ddf schreef:
> On Apr 17, 3:21 pm, Michael Austin <maus..._at_firstdbasource.com> wrote:
>> ddf wrote:

>>> On Apr 17, 12:38 pm, livefreeordie <jpittm..._at_gmail.com> wrote:
>>>> 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

>>> Yes, you don't need or want a check constraint, you want a stored
>>> procedure called from an application to do the processing:
>>> SQL> create table nominations(
>>> 2 nomination_id number,
>>> 3 year number,
>>> 4 nominator varchar2(12),
>>> 5 nominee varchar2(12),
>>> 6 nomination_data varchar2(255)
>>> 7 );
>>> Table created.
>>> SQL>
>>> SQL> create sequence nomseq
>>> 2 start with 1 increment by 1 nomaxvalue nocycle nocache order;
>>> Sequence created.
>>> SQL>
>>> SQL> create or replace trigger pop_nom_id
>>> 2 before insert on nominations
>>> 3 for each row
>>> 4 begin
>>> 5 select nomseq.nextval
>>> 6 into :new.nomination_id
>>> 7 from dual;
>>> 8 end;
>>> 9 /
>>> Trigger created.
>>> SQL>
>>> SQL> create or replace procedure nominate_emp(
>>> 2 p_nominee varchar2,
>>> 3 p_nominator varchar2,
>>> 4 p_reason varchar2
>>> 5 )
>>> 6 as
>>> 7
>>> 8 nom_ct number:=0;
>>> 9 too_many_noms exception;
>>> 10 pragma exception_init(too_many_noms, -20009);
>>> 11
>>> 12 begin
>>> 13 select count(*)
>>> 14 into nom_ct
>>> 15 from nominations
>>> 16 where nominator = p_nominator;
>>> 17
>>> 18 if nom_ct < 2 then
>>> 19 insert into nominations
>>> 20 (year, nominator, nominee, nomination_data)
>>> 21 values
>>> 22 (to_number(to_char(sysdate, 'RRRR')),
>>> 23 p_nominator,
>>> 24 p_nominee,
>>> 25 p_reason);
>>> 26
>>> 27 commit;
>>> 28
>>> 29 else
>>> 30 raise too_many_noms;
>>> 31 end if;
>>> 32
>>> 33 exception
>>> 34 when too_many_noms then
>>> 35 raise_application_error(-20009, 'Too many nominations for
>>> user '||p_nominator);
>>> 36
>>> 37 when others then
>>> 38 -- Output desired error message
>>> 39 dbms_output.put_line('-20999: Sumthin'' bad happened --
>>> error stack follows');
>>> 40 -- Output actual line number of error source
>>> 41 dbms_output.put(dbms_utility.format_error_backtrace);
>>> 42 -- Output the actual error number and message
>>> 43 dbms_output.put_line(dbms_utility.format_error_stack);
>>> 44
>>> 45 end;
>>> 46 /
>>> Procedure created.
>>> SQL>
>>> SQL> show errors
>>> No errors.
>>> SQL>
>>> SQL> exec nominate_emp(user, user, 'Because I am worth it')
>>> PL/SQL procedure successfully completed.
>>> SQL> exec nominate_emp('LARDBUCKET', user, 'Because he is worth it')
>>> PL/SQL procedure successfully completed.
>>> SQL> exec nominate_emp('ZING', user, 'Because')
>>> BEGIN nominate_emp('ZING', user, 'Because'); END;
>>> *
>>> ERROR at line 1:
>>> ORA-20009: Too many nominations for user BING
>>> ORA-06512: at "BING.NOMINATE_EMP", line 35
>>> ORA-06512: at line 1
>>> SQL>
>>> SQL> select
>>> 2 nomination_id,
>>> 3 year,
>>> 4 nominator,
>>> 5 nominee,
>>> 6 nomination_data
>>> 7 from nominations;
>>> NOMINATION_ID YEAR NOMINATOR NOMINEE NOMINATION_DATA
>>> ------------- ---------- ------------ ------------
>>> --------------------------
>>> 1 2009 BING BING Because I am worth
>>> it
>>> 2 2009 BING LARDBUCKET Because he is worth
>>> it
>>> SQL>
>>> With the commit inside the procedure you *should* be able to keep to
>>> the two nominations maximum per nominator (of course depending upon
>>> how active this nomination process can be with multiple users you may
>>> have a few slip through the cracks).
>>> David Fitzjarrell
>> You must have some serious time on your hands to actually go to all this
>> trouble... :)  I was  leaving it as a learning exercise for the OP :)- Hide quoted text -
>>
>> - Show quoted text -
> 
> 20 minutes is not "serious time", in my estimation, and I wrote that
> while waiting for an import fo complete.
> 
> 
> David Fitzjarrell

And lack of spam to react on......

Shakespeare Received on Fri Apr 17 2009 - 17:05:26 CDT

Original text of this message