[thelist] Adding Custom Info to Primary Keys in SQL

[Joshua Olson]

----- Original Message -----
From: "RUST Randal" <RRust at COVANSYS.com>
Sent: Wednesday, February 05, 2003 9:52 AM


> When I am creating primary keys that auto-increment, I'd like to add a
> prefix in front of them. So instead of "135," I get "B135."
>
> I need this value to be more specific so I can join a couple of tables.

This is going to be problematic to implement.  B135 is a string value and
135 is a numeric value.  What you are trying to do won't be directly
possible.  However, you may be able to do some wrangling to make this work
anyway:

1.  You could create a calculated field.  Check the docs of your database to
see how to do this.

2.  You could create a view on the table and create a field that converts
the numeric to a string and prepends the character.  Then, do the join
against the view.

3.  Maintain a secondary field that has this alphanumeric key.  Modify the
insert trigger to update this value accordingly based on the autonumber PK
field.  It'll look something like this:

CREATE TRIGGER mytable_insert ON mytable
FOR insert
AS
UPDATE mytable
SET mynewkey = 'B' + Convert(varchar, id)
WHERE id = @@identity

If you want to avoid triggers and extraction of the newly inserted identity
then you could just run the following after any insert:

UPDATE mytable
SET mynewkey = 'B' + Convert(varchar, id)
WHERE mynewkey IS NULL

2 and 3 will probably be the better bets, with 3 being probably the best
bet.

HTH,
-joshua