Oracle Tips and Tricks — David Fitzjarrell

July 31, 2015

What’s In A (User) Name?

Filed under: General — dfitzjarrell @ 13:44

"So each one of you agrees to disagree with whatever the other one agrees with,
but if you both disagree with the same thing, aren't you really in agreement?"
- Norton Juster, The Phantom Tollbooth

Recently a blog post caught my attention, primarily because it was talking about primary keys in a database. In the post an interesting situation was presented, one involving using a username column as the key. The point was made that because duplicate username values can be generated that, alone, wasn’t enough to guarantee uniqueness. Surrogate keys were also mentioned but dismissed because adding a sequence column didn’t make the actual username unique. Let’s look at a way a username can be generated by the actual data and a surrogate value. First, let’s create a table:


SQL>> create table employee(
  2          username        varchar2(8),
  3          first_name      varchar2(40),
  4          last_name       varchar2(40),
  5          hire_date       date,
  6          position        varchar2(30),
  7          office_loc      varchar2(40));

Table created.

SQL>>

Nothing unusual there, but now the fun part begins. The business rules for usernames are as follows:

	1	Usernames must be no longer than 8 characters
	2	Usernames must include the first initial and the first five characters of the last name
	3	Usernames must be unique

An interesting set of business rules, indeed. The first two rules are fairly easy to enforce, especially since rule 2 provides 6 characters. This leaves two characters left to ensure uniqueness. This is where the argument over natural and surrogate primary keys rears its ugly head. In this situation a ‘natural’ primary key isn’t possible since it could be that two employees will generate the same 6-character username. Let’s make this happen for the Rumenfrumper Rock Company. They have hired, over the years, 5 of the Williams sisters (Dee, Diane, Dora, Desdimona and Darcy). They are also changing vendors for their HR application and must use the table defined above. They now need to migrate their existing data and generate new usernames. This almost looks like an impossible task.

Taking a step back for a moment and examining the situation it appears that a surrogate key could help here. Unfortunately if the table is redesigned to include a user_id column we still get the problem of duplicate usernames, making it impossible for all of the sisters to use the new HR application (no matter which one of the sisters tries to register time on a timesheet the hours will all go to one timesheet and the others will show no hours). So, let’s re-think how we can use a surrogate value to generate truly unique usernames. If the generated numeric value is appended to the existing username, we get a unique username that allows every employee to use the new HR application. To do that a procedure could be used to insert new employees into the system. First, a primary key is created against the username column:


SQL>> alter table employee add constraint emp_pk primary key (username);

Table altered.

SQL>>

A procedure, like this one, could be written:


SQL>
SQL> --
SQL> -- Insert a new employee
SQL> --
SQL> -- Generate a unique username
SQL> --
SQL> create or replace procedure add_employee(p_firstnm in varchar2, p_lastnm in varchar2, p_hiredt in date, p_pos in varchar2, p_offloc in varchar2)
  2  is
  3          v_username      varchar2(8);
  4          v_max           varchar2(8);
  5          v_ct            number:=0;
  6
  7  --
  8  -- Get the last known username for the 'base' username
  9  --
 10          cursor get_max is
 11          select max(username)
 12          from employee
 13          where substr(username,1,6) = v_username;
 14
 15  begin
 16  --
 17  -- Generate the 'base' username
 18  -- from the first and last names supplied
 19  --
 20          v_username:=substr(p_firstnm, 1, 1)||substr(p_lastnm,1,5);
 21
 22  --
 23  -- Fetch the 'last' username in the database using the
 24  -- generated 'base'
 25  --
 26  -- Since each username has a two-digit number appended this will be
 27  -- the username with the highest number
 28  --
 29          open get_max;
 30          fetch get_max into v_max;
 31          close get_max;
 32
 33  --
 34  -- Extract the appended number, then add 1
 35  --
 36          v_ct := nvl(to_number(substr(v_max, 7,2)), 0);
 37          v_ct := v_ct + 1;
 38  --
 39  -- Generate the unique username
 40  --
 41          v_username:=v_username||lpad(v_ct, 2, '0');
 42
 43  --
 44  -- Insert the new employee record
 45  --
 46          insert into employee
 47          values (v_username, p_firstnm, p_lastnm, p_hiredt, p_pos, p_offloc);
 48
 49  --
 50  -- Commit the insert
 51  --
 52          commit;
 53  --
 54  -- Provide usable output when exceptions occur
 55  --
 56  exception
 57          WHEN others THEN -- Now we figure out what failed and why.
 58                -- Output desired error message
 59                dbms_output.put_line('-20999: Sumthin'' bad happened -- error stack follows');
 60                -- Output actual line number of error source
 61                dbms_output.put(dbms_utility.format_error_backtrace);
 62                -- Output the actual error number and message
 63                dbms_output.put_line(dbms_utility.format_error_stack);
 64  end;
 65  /

Procedure created.

SQL>
SQL> show errors
No errors.
SQL>

Enter new employees into the system using the procedure:


SQL>
SQL> --
SQL> -- Add new employees
SQL> --
SQL> -- All employees will generate the same 'base'
SQL> --
SQL> exec add_employee('DEE','WILLIAMS', sysdate - 1000, 'CLERK','OMAHA')

PL/SQL procedure successfully completed.

SQL> exec add_employee('DIANE','WILLIAMS', sysdate - 900, 'CLERK','OMAHA')

PL/SQL procedure successfully completed.

SQL> exec add_employee('DORA','WILLIAMS', sysdate - 800, 'CLERK','OMAHA')

PL/SQL procedure successfully completed.

SQL> exec add_employee('DESDIMONA','WILLIAMS', sysdate - 600, 'CLERK','OMAHA')

PL/SQL procedure successfully completed.

SQL> exec add_employee('DARCY','WILLIAMS', sysdate - 300, 'CLERK','OMAHA')

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Check the contents of the employee table
SQL> --
SQL> select *
  2  from employee;

USERNAME FIRST_NAME                               LAST_NAME                                HIRE_DATE POSITION                       OFFICE_LOC
-------- ---------------------------------------- ---------------------------------------- --------- ------------------------------ -----------------------
DWILLI01 DEE                                      WILLIAMS                                 03-NOV-12 CLERK                          OMAHA
DWILLI02 DIANE                                    WILLIAMS                                 11-FEB-13 CLERK                          OMAHA
DWILLI03 DORA                                     WILLIAMS                                 22-MAY-13 CLERK                          OMAHA
DWILLI04 DESDIMONA                                WILLIAMS                                 08-DEC-13 CLERK                          OMAHA
DWILLI05 DARCY                                    WILLIAMS                                 04-OCT-14 CLERK                          OMAHA

SQL>

All five sisters were entered successfully, each with a unique username. The company then hired the last sister, Dierdra:


SQL>
SQL> --
SQL> -- Add another employee
SQL> --
SQL> exec add_employee('DIERDRA','WILLIAMS', sysdate - 30, 'CLERK','OMAHA')

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from employee;

USERNAME FIRST_NAME                               LAST_NAME                                HIRE_DATE POSITION                       OFFICE_LOC
-------- ---------------------------------------- ---------------------------------------- --------- ------------------------------ -----------------------
DWILLI01 DEE                                      WILLIAMS                                 03-NOV-12 CLERK                          OMAHA
DWILLI02 DIANE                                    WILLIAMS                                 11-FEB-13 CLERK                          OMAHA
DWILLI03 DORA                                     WILLIAMS                                 22-MAY-13 CLERK                          OMAHA
DWILLI04 DESDIMONA                                WILLIAMS                                 08-DEC-13 CLERK                          OMAHA
DWILLI05 DARCY                                    WILLIAMS                                 04-OCT-14 CLERK                          OMAHA
DWILLI06 DIERDRA                                  WILLIAMS                                 01-JUL-15 CLERK                          OMAHA

6 rows selected.

SQL>

Notice the similar usernames have incremented numeric values based on the highest current login at the time of the insert. Now let’s add another employee with a different name and see what the procedure generates:


SQL>
SQL> --
SQL> -- Add another employee
SQL> --
SQL> exec add_employee('WAXAHATCHIE','BORGENFORFER', sysdate - 10, 'CLERK','OMAHA')

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from employee;

USERNAME FIRST_NAME                               LAST_NAME                                HIRE_DATE POSITION                       OFFICE_LOC
-------- ---------------------------------------- ---------------------------------------- --------- ------------------------------ -----------------------
DWILLI01 DEE                                      WILLIAMS                                 03-NOV-12 CLERK                          OMAHA
DWILLI02 DIANE                                    WILLIAMS                                 11-FEB-13 CLERK                          OMAHA
DWILLI03 DORA                                     WILLIAMS                                 22-MAY-13 CLERK                          OMAHA
DWILLI04 DESDIMONA                                WILLIAMS                                 08-DEC-13 CLERK                          OMAHA
DWILLI05 DARCY                                    WILLIAMS                                 04-OCT-14 CLERK                          OMAHA
DWILLI06 DIERDRA                                  WILLIAMS                                 01-JUL-15 CLERK                          OMAHA
WBORGE01 WAXAHATCHIE                              BORGENFORFER                             21-JUL-15 CLERK                          OMAHA

7 rows selected.

SQL>

Each new employee is added with a unique username, and this works because only one person will be entering new employees into the system and the procedure commits after each employee is added. This guarantees that the sequence will be unbroken for a given ‘base’. If this were a multi-user application such a technique would fail because duplicate usernames could be generated, which is why using transactional methods to generate numeric primary key values won’t work. And if you have more than 99 employees who generate the same username ‘base’ the inserts will fail for the table this example is using. I haven’t seen a business where 100 people all have the same 6 letter combination of initial and first five of the last name, but stranger things have happened.

This certainly isn’t the only way to address such a problem, and I’m certain that more than one enterprising developer will try another approach. My goal is to offer a different view on defining primary keys, one not solely dependent on ‘natural’ values or surrogate keys, but a combination of both.

Sometimes you agree to disagree.

Advertisements

1 Comment »

  1. I’m not much of a fan of using username as the primary key. Primary Keys should be immutable, unchanging, but what happens when Dee, Diane, Dora, Desdemona, Darcy, and/or Deirdra get married and want to change not only their legal name, but their user name as well? To change their user name in a system not utilizing surrogate keys, would require either disabling the old user account and creating a new one, or updating the username of the current record, and “every” reference to that username in every other table that includes username references.

    Comment by Sentinel — September 16, 2015 @ 16:53 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: