Re: How do I constrain this?

From: Diana Tracy <bs794_at_cleveland.Freenet.Edu>
Date: 8 Mar 1994 13:31:26 GMT
Message-ID: <2lhune$23b_at_usenet.INS.CWRU.Edu>

In a previous article, tomiii_at_mtu.edu (Thomas Dwyer III) says:

>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'))
> );

For one thing, you might want to use varchar2 instead of char in Oracle v7. Chars are trouble in my experience. Also, don't you want the primary_indicator to be not null? These are nitpicky, I know. The solution I'm proposing also requires one more column.

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

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

OK, now for what you really wanted:

create or replace trigger biur_phone_tab before insert or update of primary_indicator for each row
begin
  if :old.CHECK_PRIMARY is null then
    :new.CHECK_PRIMARY := 'X';
 else

    :new.CHECK_PRIMARY := null;
  end if;
end biur_phone_tab;

create or replace trigger aiu_phone_tab
after insert or update of check_primary
  cursor get_changed is
    select *
    from phone_tab
    where check_primary = 'X'
    for update;
  primary_count number;
begin
  for changed in get_changed loop
    if changed.primary_indicator = 'Y' then

      update phone_tab
      set primary_indicator = 'N'
      where person_id = changed.person_id
      and primary_indicator = 'Y'
      and not ( current of get_changed );
    else
      select count(*)
      into primary_count
      from phone_tab
      where person_id = changed.person_id
      and primary_indicator = 'Y';
      if primary_count = 0 then
	update phone_tab
	set primary_indicator = 'Y'
      end if;

    end if;
  end loop;
end aiu_phone_tab;

I don't know if this will meet your needs and I'm about to get cut off, but play with the idea. The two triggers are required to circumvent the mutating table problem. Good luck! Send any questions by email and I'll think about it further.

-- 
Diana Tracy, System Designer		-- Excitement, Adventure
bs794_at_cleveland.Freenet.Edu		-- and Really Wild Things

Received on Tue Mar 08 1994 - 14:31:26 CET