|
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
|
