How do I constrain this?

I have the following table in an Oracle v7 database:

	create table phone_tab (
		person_id		char(9) not null,
		phone			char(10) not null,
		primary_indicator	char(1)
			constraint check_primary_indicator
			check (primary_indicator in ('Y', 'N'))
	);

Each person may have an unlimited number of phone numbers but only one can be the primary phone number at any given time. How can I create a constraint/trigger/whatever which enforces the statement:

    For every person_id there must be exactly one row where primary_ind='Y'

Is this even possible? Any suggestions would be appreciated.

Thanks,
Tom.III
tomiii_at_mtu.edu Received on Thu Mar 03 1994 - 23:37:41 CET