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

From: ddf <oratune_at_msn.com>
Date: Fri, 17 Apr 2009 13:29:31 -0700 (PDT)
Message-ID: <a6fa4b2a-4509-4082-854b-841e1b9866a5_at_l5g2000vbc.googlegroups.com>

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 Received on Fri Apr 17 2009 - 15:29:31 CDT