Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Question: CHECK constraint or trigger?

Re: SQL Question: CHECK constraint or trigger?

From: John Wiberg <Nobody_at_NoWhere.com>
Date: 1998/03/04
Message-ID: <34FD6D69.3BD0@NoWhere.com>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US