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

I would take NUMBER as type of CURRENT (maybe with DEFAULT 1 & check for
>0 ) & put a unique index on (NAMEID, CURRENT).
michael simons

-- 
---------------------------------------------------------
.. and on the third day he rebooted into Linux-1.3.84 ...
   (Linus Torvalds, Easter Kernel Release 1996)