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 13:32:22 -0500
Message-ID: <cV3Gl.14788$%54.11447_at_nlpi070.nbdc.sbc.com>
livefreeordie 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
Date: Fri, 17 Apr 2009 13:32:22 -0500
Message-ID: <cV3Gl.14788$%54.11447_at_nlpi070.nbdc.sbc.com>
livefreeordie 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
one method would be to add the data using a procedure that would limit the number.
input: nominator input: nominee input: other nominee data.... output: ret_status
select count(*) into variablea from table where nominator=:nominator
if variablea >=2 then return <some bad status>
else
insert the data
return good status.
Received on Fri Apr 17 2009 - 13:32:22 CDT