GUID in databases other than SQL Server

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 so开发者_如何学Golve 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...

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.

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.

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

I have never had any trouble using Guids. We used Guid.Comb in a system with many records (millions) and had no trouble because of the Guids themselves. The selling point for me is that I can generate the Ids before i persist something to the database. Even on the client. Which is very helpful in CQRS scenarios. The only thing that I think you should also consider is human readability. It's hard to look at the database and match records in a lets say master/detail scenario.

And a note on Firebird... Uuid is written as an octet. And most clients that I've used to manage the database can't represent those in a decent format. So it's usually just displayed as a couple of characters (probably by just decoding a byte array as a string). I don't know about other providers though. SQLServer Management Studio for example shows them just fine.