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

Dig Deeper on Oracle and SQL