Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Location
    Atlanta, GA USA
    Posts
    20

    Question Unanswered: Best practice for "Unique Active Row"

    Is there a best practice (or just a good idea) for the following scenario? I have this table:
    create table USERS (
    user_id int primary key,
    user_name varchar2(10) unique
    );

    Right now my user_names are unique. I'd like to add support for reusing user names, but I don't want to delete the old users because there are foreign keys that reference user_id.

    What can I add to this table to allow reuse of user_names while leaving the old ones in there? Looking up "active" users needs to be an indexed operation, preferably a unique scan. I had considered adding an is_active column, but:
    • I can't enforce it via unique key on (user_name,is_active) because is_active can have multiple "false" values.
    • I can't check it with a trigger because the table is mutating.

    Surely this is a common problem, I just don't really know what to google for.

    Thanks in advance,
    Philip

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can use a unique function-based index to achieve this:

    create table USERS (
    user_id int primary key,
    user_name varchar2(10),
    is_active varchar2(1) not null check (is_active in ('Y','N'))
    );

    create unique index username_uk on users
    ( case when is_active='Y' then user_name end );

    That will uniquely index only the usernames for active users.

  3. #3
    Join Date
    Nov 2005
    Location
    Atlanta, GA USA
    Posts
    20
    Ah, that's a great idea. Thanks.

    I wonder if using a separate index on (user_name,is_active) would even make a difference for lookups. I don't expect this to happen a lot, so there usually will only be one row per user_name anyway. I guess I'll do some tests when I get some real data to test with.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •