Re: Check for no more than two entries of each value in a column?
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 17 Apr 2009 20:28:50 +0200
Message-ID: <49e8ca69$0$189$e4fe514c_at_news.xs4all.nl>
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
Date: Fri, 17 Apr 2009 20:28:50 +0200
Message-ID: <49e8ca69$0$189$e4fe514c_at_news.xs4all.nl>
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 Received on Fri Apr 17 2009 - 13:28:50 CDT