I am trying to create a table which have "id" as its primary key. the property "id" is a string with fixed length and consists of characters and numbers， e.g. ABC45125. very easy to automatically generate its value in database if it is an integer but I have no idea when it is a string. any idea about it? I will appreciate.

so you can't use the inbuilt autonumber column.....

I suppose it depends on what the alpha component refers to, how that is made up.

it also depends on whether the numbers have to be contiguous.. nothing upsets an accountant more that seeing a list of numbers which are non contiguous.

its actually fairly simple to do, once you have sorted out your true requirements.

I've used a composite alphanumeric on numerous occasions (often the alpha component adds more information tot he numeric (eg where an acconting system needed to identify the period as part of the invoice number). they wanted a quick and simple method of identifying what year and month a transaction was raised and they wanted there invoice numbers masked so that their customers and competition couldn't easily identify how much trade they were doing.


so over to you...
what does the ABC but represent, how does that change?
do you need contiguous numbers?
what causes the number to change in what sequence?

bear in mind that to most computers the letter A is 65, a is 97

this is the Database Concepts & Design forum

the concept you've described is pretty clear, but i'm afraid that if you want a working solution, you will have to tell us which database system you're using -- then we can move this thread to the appropriate forum and get to work...

I use mysql

thread moved to mysql

if you want automatically assigned numbers, you must use an INTEGER AUTO_INCREMENT column

you can "fake" the alphabetic/numeric codes with concatenation whenever you need to retrieve the code from the table

SELECT CONCAT('ABC',RIGHT(CONCAT('0000',id),5) AS my_id ...