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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.