SQL Question: CHECK constraint or trigger?

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 Received on Tue Mar 03 1998 - 00:00:00 CST