Re: preventing 2 rows with same "flag" set

Ok....

SQL> l
[Quoted] 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...
Received on Fri Oct 13 2000 - 14:15:28 CEST