Generating unique incremental integer ID for database tables

[Claire]

Hi,
I can imagine this question has been brought up before but I've spent all
morning trying to find what I need on google without success.
My application sits on Mysql or MS sql server engines and to date I've just
been using auto-incremental int64 fields to generate my RecID field.
I know that in the future the database will need to support multiple sites,
so theres also a SiteID field in there too.

example table ABC
Primary Key = (int64)ABC.RecID + (int32)ABC.fk_Sites_RecID

In the not too distant future multi sites WILL be needed, so I need to
switch out auto-incrementation and generate record ids myself so that the
sites can be synchronised across to a central server (another noob terror to
come)
I don't want to use triggers or stored procedures as I want my database to
be as simple/clean as possible.

How should I generate my "autoincremental" unique RecIDs please?

thank you

 

[GArlington]

Hi,
I can imagine this question has been brought up before but I've spent all
morning trying to find what I need on google without success.
My application sits on Mysql or MS sql server engines and to date I've just
been using auto-incremental int64 fields to generate my RecID field.
I know that in the future the database will need to support multiple sites,
so theres also a SiteID field in there too.

example table ABC
Primary Key = (int64)ABC.RecID + (int32)ABC.fk_Sites_RecID

In the not too distant future multi sites WILL be needed, so I need to
switch out auto-incrementation and generate record ids myself so that the
sites can be synchronised across to a central server (another noob terror to
come)
I don't want to use triggers or stored procedures as I want my database to
be as simple/clean as possible.

How should I generate my "autoincremental" unique RecIDs please?

thank you

Look into UUID

 

[Peter Bromberg [C# MVP]]

Having a primary key that is the sum of two integers could get you in a lot
of trouble, since there are many combinations of two integers that could add
up to the same number. But in general, you would need to get the SQL MAX(ID)
from the table, increment by 1, and that would be your next id integer.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short Urls & more: http://ittyurl.net

 

[Claire]

No, Im sorry if my shorthand is wrong Peter
I meant there are 2 fields in the primary key. (Im not daft enough to add
the values together)
1) 64 bit integer RecID field
2) 32 bit integer fk_Sites_RecID (foreign key, Sites table, RecID field)

but thank you for the MAX(ID) suggestion
Claire

 

[Claire]

How should I generate my "autoincremental" unique RecIDs please?

Look into UUID

Thank you for the UUID suggestion
I have to stick with my incremental big ints as they're more likely to be
compatible over a selection of different servers than guid/uuids. (ie the
version of MySQL I'm using doesnt support them)

Claire

 

[Steve Gerrard]

(inline comments)

example table ABC
Primary Key = (int64)ABC.RecID + (int32)ABC.fk_Sites_RecID

In the not too distant future multi sites WILL be needed, so I need to
switch out auto-incrementation and generate record ids myself so that
the sites can be synchronised across to a central server (another
noob terror to come)

I don't really follow that. If each site uses a different SiteID, then the same
RecID value can occur at multiple sites, no? That would seem to be the point of
including the SiteID in the primary key in the first place.

I don't want to use triggers or stored procedures as I want my
database to be as simple/clean as possible.

That would not be my definition of a simple and clean database, but to each her
own...

How should I generate my "autoincremental" unique RecIDs please?

I would be tempted to create a single column primary key out of RecID, since
they are so useful in code. Can you set a starting value for the auto-increment
of each table at each site? If you assumed that you would have less than 10
million sites, and each site would have less than 10 billion records per table,
you could set the starting value at each site, using offsets of 10 billion, such
that they would never overlap.

 

[GArlington]

Thank you for the UUID suggestion
I have to stick with my incremental big ints as they're more likely to be
compatible over a selection of different servers than guid/uuids. (ie the
version of MySQL I'm using doesnt support them)

Claire

Anything will support UUID - because you can always store it as a
string.
Generate new UUID in your script/program and pass it to your SQL...

 

[Christopher Van Kirk]

There is a problem with UUID, though. They're huge, especially when
converted to a string form.

 

[GArlington]

There is a problem with UUID, though. They're huge, especially when
converted to a string form.

<snip>
http://en.wikipedia.org/wiki/UUID
<extract>
In its canonical form, a UUID consists of 32 hexadecimal digits,
displayed in 5 groups separated by hyphens, in the form 8-4-4-4-12 for
a total of 36 characters...