CODEWITHSUNDEEP
mysqlsqldatabase
Kanin Peanviriyakulkit
Kanin Peanviriyakulkit

Reputation: 520

Mysql Primary ID with no auto increment?

I work on project with c# and mysql to create POS program that can use when PC offline if PC online it will connect to remote DB server.

but I found the problem that my Order ID use on many computer like this.

PC1 : 100001 100002 ...
PC2 : 110001 110002 ...

So I decide to make my Primary Order ID with no auto increment. Question is can I do it ? It will look like this

order_ID
100001
120001
100002
110001
110002
100003

If it look like this, Can I use it to Search function? or (index it)

Because it will not same number I have 2digis number for PC client.

100001
10 is prefix for PC and 0001 is order number

my complete table on DB Server will be like this

id(not use auto increment but Primary key)
100001
130001
100002

and on my offline PC will be like this

10 is PC1
id        prefix     number(this will use auto increment and Primary key)
100001      10        0001
100002      10        0002

13 is PC2
id        prefix     number(this will use auto increment and Primary key)
130001      13        0001

so when I want to search order I will "Select from ordertable where id=130001"

that right ? if that right, is it a low performance?

Thank you.

Upvotes: 0

Views: 125

Answers (1)

Marcus Adams
Marcus Adams

Reputation: 53830

Your solution is fine, though it limits you to 9999 records per PC. Your primary key need not be an auto-increment integer. The only requirement of a primary key is that it's unique.

Yet, assuming InnoDB, if there are a lot of inserts, you still might consider having a surrogate auto-increment integer as the primary key and simply add an additional column with a unique index for the "id". With InnoDB, the primary key is a clustered index. Inserts into a clustered index are faster (and cause less issues with concurrency) when done in ascending order.

Upvotes: 1

Related Questions