Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Question: CHECK constraint or trigger?
Todd Owers wrote:
>
> I have an application which tracks people and their addresses. The nature
> of the application is that a person can have more than one address. If a
> person has multiple addresses, I need to designate one (and only one)
> address as that person's current address. Can this be done with a
> table-level CHECK constraint, or is a trigger required?
>
> The structure of the ADDRESS table is as follows:
>
> ADDRESSID VARCHAR2(10) PRIMARY KEY
> NAMEID VARCHAR2(10) FOREIGN KEY
> ADDRESS VARCHAR2(35)
> CITY VARCHAR2(15)
> STATE VARCHAR2(2)
> ZIP_CODE VARCHAR2(9)
> CURRENT VARCHAR2(1) CHECK (CURRENT IN ('Y','N'))
> DATE_ENTERED DATE
> LAST_UPDATED DATE
>
> For each NAMEID, only one row can have CURRENT = 'Y'. Can this be done
> with a table-level CHECK constraint, or do I need a trigger to loop through
> all rows for the given NAMEID to count how many occurrences of CURRENT =
> 'Y'? Either way, I'd appreciate a suggestion on the most efficient code.
>
> Thanks in advance for your help.
>
> Todd Owers
> toddo_at_gcr1.com
Try changing the check constraint to (CURRENT='Y'). That will allow
values of 'Y' or NULL, NULL replacing 'N' in your application logic.
Then add a unique key constraint on (NAMEID, CURRENT). This combination
of constraints will allow one instance of NAMEID + 'Y' and multiple
instances of NAMEID + NULL.
I find that letting Oracle enforce this sort of thing is preferable to me developing and maintaining triggers to do it.
Hope this helps,
John Received on Wed Mar 04 1998 - 00:00:00 CST