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

From: livefreeordie <jpittman2_at_gmail.com>
Date: Sat, 18 Apr 2009 05:06:07 -0700 (PDT)
Message-ID: <4442e894-51e9-41fc-b157-54bb8dcd9ff6_at_a7g2000yqk.googlegroups.com>

On Apr 17, 2:28 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> livefreeordie schreef:
>
>
>
>
>
> > 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 could create a trigger, checking the number of records for one
> nominator on insert of a new one, and let it fail if two already exist.
>
> OR: create an extra column for nominee number, with a constraint that it
> can be 1 or 2, and let the nominator fill that column (1 for first
> nominee, 2 for second) and create a unique constraint on nominator,
> nominee number, nomination year. This way you only have to change the
> check constraint on nominee number when more nominees are allowed.
>
> OR: other options....
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

Ah that's a good solution. Thanks. Received on Sat Apr 18 2009 - 07:06:07 CDT