Re: Check for no more than two entries of each value in a column?
Date: Sat, 18 Apr 2009 21:27:43 +0200
Message-ID: <74unu3F15udsnU1_at_mid.individual.net>
On 18.04.2009 14:06, livefreeordie wrote:
> 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.
If I am not mistaken this one will fall into the "mutating table" trap.
>> 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.
This makes additional business logic necessary, especially determining the appropriate number before the insert.
>> OR: other options....
>>
>> Shakespeare- Hide quoted text -
>>
>> - Show quoted text -
>
> Ah that's a good solution. Thanks.
Here's another option: create another table with PK (nominator, year) and additional column count (default = 0, check constraint which limits values to 0,1 or 2). Create an insert trigger on the first table which inserts or updates the counter table. Create update and delete triggers on the first table which update counter table.
This version is also concurrency safe because you have exclusive access on the counter records per (nominator, year).
Kind regards
robert Received on Sat Apr 18 2009 - 14:27:43 CDT