Re: preventing 2 rows with same "flag" set

Eric Givler wrote:
>
> Ok....
>
> SQL> l
> 1 > alter table application_user_profiles
> 2 > add constraint uk_app_user_profiles unique ( user_eid,
> primary_location_Flag ) using index
> 3 > TABLESPACE myindex_TS
> 4 > exceptions into exceptions
>
> SQL> /
> alter table application_user_profiles
> *
> ERROR at line 1:
> ORA-02299: cannot enable (lameduck.uk_app_user_profiles) - duplicate keys
> found
>
> SQL> select count(*) from exceptions;
>
> COUNT(*)
> ----------
> 12236
>
> 1 row selected.
>
> SQL> select count(*) from application_user_profiles;
>
> COUNT(*)
> ----------
> 13101
>
> 1 row selected.
>
> This looks like the unique constraint is storing the rows that have the
> primary_location_flag as null too. Otherwise, I'd think we'd only have one
> exception:
>
> SQL> select user_eid, count(*)
> 2 from application_juser_profiles
> 3 where primary_location_Flag ='Y'
> 4 group by user_eid
> 5 having count(*) > 1
> 6
> SQL> /
>
> USER_EID COUNT(*)
> ---------- ----------
> 9289513 2
>
> 1 row selected.
>
> > If you had a unique key on user_eid and flag (restricted to null and Y),
> > then at commit time, if someone's done something wrong, they'll get a
> > unique constraint violation...
> >
> > Getting forms to pick that up earlier may be the challenging bit...

Sorry - my mistake - of course the unique constraint (and null's all being considered unique) only makes sense for a single column (or all columns being null for a composite)...

Thus using a trigger to merge the two columns you want to keep unique into a third should assist...So you'd end up with something like:

create table test1 (
  prim number,
  loc number,
  locflag varchar2(1),
  primloc varchar2(10)
);

alter table test1 add constraint test1_pk primary key ( prim, loc );

alter table test1 add constraints test1_ck check (locflag in ('Y','N'));

alter table test1 add constraint test1_uk unique ( primloc );

create or replace trigger test1_trg
before insert or update on test1
for each row
begin
  if :new.locflag = 'Y' then
    :new.primloc := :new.prim||'Y';
 else

    :new.primloc := null;
  end if;
end;
/

HTH
Connor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk
(faster/mirrored at http://www.oradba.freeserve.co.uk)

Its not the voices in my head that bother me... 
  its the voices in yours.