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

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

Original text of this message