How to generate a transaction number?

I was thinking of formatting it like this

TYYYYMMDDNNNNNNNNNNX (1 character开发者_运维技巧 + 19 digits)

Where

• T is type
• YYYY is year
• MM is month
• DD is day
• N is sequencial number
• X is check digit

The problem is, how do I generate the sequencial number? since my primary key is not an auto increment integer value, if it was i would use that, but its not.

EDIT can I have the sequencial number resets itself after 1 day (24hours).

• P201012080000000001X <-- first transaction of 2010/12/08
• P2010120810000000002X <--- second transaction of 2010/12/08
• P201012090000000001X <--- First transaction of 2010/12/09

(X is the check digit)

The question is meaningless without a context. Others have commented on your question. Please answer the comments. What is the "transaction number" for; where is it used; what is the "transaction" that you need an external identifier for.

Identity or auto-increment columns may have some use internally, but they are quite useless outside the database.

If we had the full schema, knowing which components are PKs that will not change, etc, we could provide a more meaningful answer.

At first glance, without the info requested, I see no point in recording date in the "transaction" (the date is already stored in the transaction row)

You seem to have the formula for your transaction number, the only question you really have is how to generate a sequence number that resets each day.

You can consider the following options:

• Use a database sequence and a scheduled job that resets it.

• Use a sequence from outside the database (for instance, a file or memory structure).

• With the proper isolation level, you should be able to include the (SELECT (MAX(Seq) + 1) FROM Table WHERE DateCol = CURRENT_DATE) as a value expression in your INSERT statement.

Also note that there's probably no real reason to actually store the transaction number in the database as it's easy to derive it from the information it encodes. All you need to store is the sequential number.

You can track the auto-incs separately.

Or, as you get ready to add a new transaction. First poll the DB for the newest transaction and break that apart to find the number, and increase that.

Or add an auto-inc field, but don't use it as a key.

You can use a uuid generator so that you don't have to mind about a sequence and you are sure not to have collision between transactions.

eg :

in java :

java.util.UUID.randomUUID()

05f4c168-083a-4107-84ef-10346fad6f58
5fb202f1-5d2a-4d59-bbeb-5bcabd513520
31836df6-d4ee-457b-a47a-d491d5960530
3aaaa3c2-c1a0-4978-9ca8-be1c7a0798cf

in php :

echo uniqid()

4d00fe31232b6
4d00fe4eeefc2
4d00fe575c262

there is a UUID generator in barely all languages.

A primary key that big is a very, very bad idea. You will waste huge amounts of table space unnecessarily and make your table very slow to query and manage. Make you primary key a small simple incrementing int and store the transaction date in a separate field. When necessary in a query you can select a transaction number for that day with:

SELECT ROW_NUMBER OVER (PARTITION BY TxnDate ORDER BY TxnID), TxnDate, ...

Please read this regarding good primary key selection criteria. http://www.sqlskills.com/BLOGS/KIMBERLY/category/Indexes.aspx