[thelist] Adding Custom Info to Primary Keys in SQL
Joshua Olson
joshua at waetech.com
Wed Feb 5 09:08:00 CST 2003
----- 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
More information about the thelist
mailing list