Oracle SQL auto increment for char

2
votes

I am using Oracle SQL Developer to build a database, I got a city table with city_id [PK] and city_name, I wish to make my city_id as a char and my data would look like below

|----------------------|
| CT01 | MEXICO CITY   | 
| CT02 | NEW YORK CITY |
| CT03 | LONDON        |
| CT04 | SYDNEY        |
|----------------------|

Furthermore, I would like to make my primary key to have a function of auto increment, which mean when I insert a city_name, the city_id will increase by 1, in this case if I insert 'HONG KONG', it's city_id will auto be set as 'CT05'.

I had done some research online, what I found is using a trigger, but it only work for number type data.

An additional question, when I creating a table, if my primary key is a number, I would like to store it as the format like this -> 000001 what should I do to make my data look like this?

Thank you.

1
sqloracleoracle-sqldeveloperauto-increment
Keep it simple, use integer data type instead! - jarlh
Why do you want to store these things with those values? Presumably no-one but the program (and, I suppose, developers/debuggers) will see those columns, so what does it matter that they are numbers? IMO, stick with sequences, and then if you absolutely must display them as strings, add a virtual column or create a view with the required concatenation/LPADing - Boneist
@jarlh do you know how to set my data to look like in my additional question? for example if I insert 20, it will be store like 000020 - user7934618
Answer 2: Integer values are stored as ‭0010101111010101 etc‬. You are talking about a presentation format. - jarlh

1 Answers

7
votes

Don't do it.

Primary keys (and keys in general) are used to provide uniqueness for the rows. They are not supposed to be sexy or nice looking. They are internal identifiers. If you are trying to do this, it means you want to expose the PK value somewhere... something that's not the purpose of the PK in the first place.

If you really want a nice looking identifier, then you can create a secondary "generated" column with a nice format as you want. That column can include extra formatting such as dashes (SSN for example), prefixes (your case), suffixes, etc.