How to add a 12 digit auto incremental field in sql?

[Gokulnathvc]

How to add a 12 digit auto incremental field in sql? I would like to create an application in Qt which has 3 fields, name, date of birth and pincode. If user gives all the information including the pin code, it should be inserted in the database. But the unique id field will be updated automatically with the pincode (6 digit)+ 6 digit.. For instance, if the user gives the pincode as 641001 then, it should be added as 641001000001, and if another user with same pin code enters the details, then it should be added as 641001000002. And if another user with different pincode 552145 then it should be added as 552145000001.. and so on.. It should check for pincode if already exists, then add 1 to the last digit and update it. and it should be unique.

[Santosh Reddy]

Qt does not has any built-in class to do so. I don't think one can find such a class in any programming framework. It is highly application specific.

You will have to write for yourself using some help from QString and QRegExp


How to add a 12 digit auto incremental field in sql?
I don't think SQL has this ability.

I have a suggestion, instead of concatenating the zip and serial number, have zip code as a field, and serial number in another field. Then zip and serial fields put together will be the primary key. That way querying the rows with same zip code will be possible using standard SQL, an then you could look for the largest serial number.

[ChrisW67]

My heart sinks every time is see yet another database "design" with keys encoding information for mutable fields. I've seen a lot of them in my time and it rarely ends well.

The unique identifier of a database record should either be:

An immutable natural key based one or more of the actual data fields in the record, or
An immutable artificial identifier (surrogate key (http://www.wikipedia.org/wiki/Surrogate_key)) that has no intrinsic meaning and nothing to do with the data in the record.

Your data may occasionally provide the first, though its generally not the case, and most SQL implementations have mechanisms to do the latter with little effort (e.g. Sqlite's INTEGER PRIMARY KEY). Using a database key that has some "meaning" to the user is subject to changing whims... and they will change. Using a database key that can change just makes handling data uglier because you have to stay on top of primary key changes caused by changing the data fields it is derived from, and then foreign key references that are broken as a result. Eventually you will miss something and the database integrity is broken.

If the user expects to see a bastard "key" like the one you describe then please build it for display only, do not use it as the basis of a database key.