Alphanumeric primary key
I want an alphanumeric primary key like abc123, abc124, abc125 and so on. Can I have primary key like this? If yes, please tell me how to implement it.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Yes, you can, but the implementation leaves a lot to be desired.

First of all, you can declare any type of column as the primary key, as long as its values will always be not null and unique. So your alphanumeric key could be declared as CHAR(7) and it would work just fine, provided that you assign the new values yourself when inserting rows.

Most databases offer an automatic numbering feature for numeric datatypes. Microsoft SQL Server uses IDENTITY, Access uses COUNTER or AUTOINCREMENT, MySQL uses AUTO_INCREMENT, Oracle uses SEQUENCE, and so on.

Unfortunately, there's no way to "append" a prefix to an automatically generated number, at least not in the table. You could declare a view to accomplish the desired result. For example, in SQL Server:

create table sample
 ( id integer identity(123,1)
 , descr ... )



create view sampleview
 ( alphanumericid
 , descr ... )
as
select 'abc' + cast(id as char(3)) 
     , descr ...
  from sample

Notice that the IDENTITY column is "seeded" to start at 123 and increment by 1.

Personally, I find this implementation awkward, and it may have detrimental implications on the performance of joins.

My philosophy is that a primary key should be either totally natural or totally surrogate. Automatically incrementing columns make great surrogate keys. Under normal circumstances, you would never reveal the values of a surrogate key to a user, so be careful if you find yourself wanting to "append" some sort of meaningful information onto a surrogate key. This suggests that the user may infer something from its values. In that case, a fully natural key may be a better choice.

For More Information


This was first published in February 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top