Sql-server – Database Schema with a shared identity Field

You could turn off automatic identity management and allocate your ranges manually. Another option could be forcing a range re-allocation by issuing sp_msrefresh_publisher_idrange and specifying the range boundaries.

However, both options would not guarantee that the lTID column is populated with ever-increasing values: you will always have at least one range for the publisher and one range for the subscriber and if users are inserting at both sites you can't have ever-increasing IDs.

This is by design and there's no way around it.

If your goal is keeping the identity values generated at the subscribers ever-increasing, that's a whole different story and it can be achieved with one of the above options.

You can have almost any field as the primary key and index it as such. However it would be down to the logic further up the stack (your scripting language most probably to provide unique IDs. You will almost definitely find yourself making additional queries to find unused IDs as the DB grows.

One approach to alpha numeric to numeric conversion would be a key table int(11) Auto in one col and VarChar(18) in the other. Thus you would actually be using common or garden numeric keys on the tables but would have a user handle them in the keys table. You would also have unique IDs across all tables and thus the user key would also implicate table as well. Simply setting the user key as indexed, unique would mean that you would try to write the key and if the row insert was a success then you are good to use the ID (and/or the key depending on what you are doing) but on a fail you would need a new key and could bounce back to the user for a new reference.

However have you considered using numeric keys in the data logic layer but showing user labels in the UI layer thus users can call their object Bob, Harry or Alpha123456FooBar1 without strange plumbing lower down to accommodate them.

In your case you want to show the user a concat key which at the data layer needs to be stored as two separate keys. This concat key seems from what you have said to define a singular one-to-one relationship which is not really it's job. It sounds like what you want is a key to the relationship which brings us back to a lookup table. here you would have the objects table use numeric keys (from the object and other object tables) and provide a unique text value (MD5/SHA1/etc (of the key concat) might work 99.9% of the time) as the label which could be enforced with index, unique.

You could also limit the int size of the object key to four digits (9999 possible entries) in value and (I guess) use VarChar keys in the other table (if you must). Thus if the user requests 0001abceFGhJk123 you can split that into 0001 (1) and abceFGhJk123.

This is far from ideal as numeric auto provided keys are so much easier to deal with. Then again you could create a lookup table of separate keys and concat-key/label (as per the universal ID I mentioned before) if that is really important to you.

One workaround (if you are determined that the users must be able to have alpha numeric keys but you know that numeric keys are easier at the data layer) is to use numeric keys at the data layer and convert the numeric base to show letters to the user. For example shifting to base16 would give you 0-9 and a-f (case insensitive) and this sort of conversion is built in to most languages, or base36 would give you 0-9 and a-z (case insensitive) while base62 would give you 0-9 a-z and A-Z although to be honest you would need a significantly huge data set to make a 14 character data stub that was not mostly padded out with zeros. MySQL lets you override the auto value with a number of your own (provided it does not yet exist) and if these key values are coming from users shifting down from a base to base10 might work.

Whatever approach you use you will be very grateful, down the line, that you kept it simple at the database layer and kept your keys separate for actual primary key data.