Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.

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 last published in February 2003

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close