GUID in databases other than SQL Server

Question

Question: I'm planning the database for one of my programs at the moment.

I intend to use ASP.NET MVC for the user backend, the database being on Linux and/or on Windows.

Now, even if I would only make it for windows, I had to take into account, that different customers use different database systems. Now, I figured I use nHibernate, then I can put everything in the code, and it works on all mayor databases, such as Oracle/Sybase/MS/PostGre/MySQL/Firebird.

My probem now is GUIDs. SQL Server uses GUIDs, while the rest uses integer auto-increment as primary keys. While auto-increment is better in theory, it creates problems keeping multiple databases in sync, or problems manually changing things, which requires CSV import/export...

Now, because of the inherent problems with autoid in practise, I like the GUID system better. And since a guid is a 36-character string, I could use varchar(36) as a primary-key, but a varchar as GUID, might just not be an ideal solution...

How would you solve this problem/what do you use as primary-key ? Or how do you evade the auto-increment problems, say insert a csv file without changing the autoid...

Answer 1

You could also consider generating a primary key which is a combination of auto-increment (i.e. setting up a sequence) and an unique identifier of the machine it was generated on, maybe using the MAC address.

See this for a discussion.

This way you have a locally unique (thanks to the sequence) ID which is also globally unique (thanks to the MAC address part).

I know, I know, you can spoof a MAC address but it's up to you to decide if this is really a risk in your domain. Also, the ability to spoof it could be handy when you test your code.

---

Please explain better what happens when a new customer DB is born. Will it be registered on the Server? If yes, you can assign a DB-id on the server, and use it in lieu of the MAC address, just assign a number to each new DB and use it along with the sequence.

Basically, if you want an "unique DB instance ID" to avoid "table id" collisions, you have only two choices:

1) Server assigns the DB ID whenever a new DB is added

2) Client autogenerate a unique ID, and this usually needs using the MAC address, either "raw" or processed somehow.

I honestly can't see alternatives given your current description of your problem.

Answer 2

A Guid key using the guid.comb generator key is usable in any database, even if it doesn't have Guid as a native type.

Answer 3

Oracle and PostgreSQL support GUIDs as well, there is no need so use sequences there (and of course Diego is right: if you use your own algorithm to create GUIDs you can always store use a varchar column with your own generated GUID)

Note that it's spelled PostgreSQL, never PostGre