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

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Fri, 17 Apr 2009 15:21:35 -0500
Message-ID: <is5Gl.5666$Lr6.380_at_flpi143.ffdc.sbc.com>

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 :) Received on Fri Apr 17 2009 - 15:21:35 CDT