Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a simple way to create a unique integer key from a two-integer composite key?

For various reasons that aren't too germane to the question, I've got a table with a composite key made out of two integers and I want to create a single unique key out of those two numbers. My initial thought was to just concatenate them, but I ran into a problem quickly when I realized that a composite key of (51,1) would result in the same unique key as (5,11), namely, 511.

Does anyone have a clever way to generate an integer out of two integers such that the generated integer is unique to the pair of start integers?

Edit: After being confronted with an impressive amount of math, I'm realizing that one detail I should have included is the sizes of the keys in question. In the originating pair, the first key is currently 6 digits and will probably stay in 7 digits for the life of the system; the second key has yet to get larger than 20. Given these constraints, it looks like the problem is much less daunting.

like image 839
abeger Avatar asked Nov 16 '09 21:11

abeger


1 Answers

You can mathematically prove this is impossible if you want the resulting key to comprise the same number of bits as its two components. However, if you start with two 32 bit ints, and can use a 64 bit int for the result, you could obviously do something like this:

key1 << 32 | key2

SQL Syntax

SELECT key1 * POWER(2, 32) + key2
like image 140
recursive Avatar answered Sep 21 '22 00:09

recursive