Re: How do I constrain this?

From article <2l5orl$9ei_at_bambam.cts.mtu.edu>

        by tomiii_at_mtu.edu

> 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.
>

	Why not make person_id and primary_indicator the primary key
	for this table?

	create table phone_tab (
		person_id		char(9),
		phone			char(10),
		primary_indicator	char(1),
		PRIMARY KEY	(person_id, primary_indicator) 
		);

	This will ensure that the table has a unique row for every 
	combination of person_id and primary_ind.  It seems rather
	too simple to me.  No complicated triggers are needed.

	Refer to your Application Developer's Guide for specifying
	the primary key.

							Yoichi Tamura
							y-tamura_at_nri.co.jp