Preventing Duplicate Keys Between Multiple Databases

Question

I'm in a situation where we have a new release of software coming out that's going to use a separate database (significant changes in the schema) from the old. There's going to be a considerable period of time where both the new and the old system will be in production and we have a need to ensure that there are unique IDs being generated between the two databases (we don't want a row in database A to have the same ID as a row in database B). The databases are Sybase.

Possible solutions I've come up with:

1. Use a data type that supports really large numbers and allocate a range for each, hoping they never overflow.
2. Use negative values for one database and positive values the other.
3. Adding an additional column that identifies the database and use the combination of that and the current ID to serve as the key.
4. Cry.

What else could I do? Are there more elegant solutions, someway for the two databases to work together? I believe the two databases will be on the same server, if that matters.

Answer 1

GUIDs or Globally Unique IDs can be handy primary keys in this sort of case. I believe Sybase supports them.

Edit: Although if your entire database is already based around integer primary keys, switching to GUIDs might not be very practical - in that case just partition the integer space between the databases, as GWLlosa and others have suggested.

Answer 2

Pre-seed your new Database with a # that is greater than what your old database will reach before you merge them.

I have done this in the past and the volume of records was reasonably low so I started my new database at 200,000 as the initial record number. then when the time came, I just migrated all of the old records into the new system.

Worked out perfectly!

Answer 3

GUIDs are designed for this situation.

Answer 4

I've seen this happen a few times. On the ones I was involved with, we simply allocated a sufficiently large ID space for the old one (since it had been in operation for a while, and we knew how many keys it'd used, we could calculate how many more keys it'd need for a specified 'lifespan') and started the "ID SEQUENCE" of the new database above that.

I would recommend against any of the other tricks, if only because they all require changes to the 'legacy' app, which is a risk I don't see the need to take.

Answer 5

In your case I would consider using uniqueidentifier (GUIDs) as datatype. They are considered unique when you create them using the system function newid().

CREATE TABLE customer (
   cust_key UNIQUEIDENTIFIER NOT NULL
            DEFAULT NEWID( ),
   rep_key VARCHAR(5),
   PRIMARY KEY(cust_key))

Answer 6

Use the UNIQUEIDENTIFIER data type. I know it works in MS SQL Server and as far as I can see from Googling, Sybase supports it.

http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbrfen9/00000099.htm

Answer 7

If you will be creating a similar number of new items on each database, you can try even ids on one database, odd ids in the other.