Is there an easy way to create a unique integer key from a dual-purpose composite key?

Question

For various reasons, which are not very relevant for the question, I have a table with a composite key consisting of two integers, and I want to create a single unique key from these two numbers. My initial thought was simply to concatenate them, but I quickly ran into a problem when I realized that the composite key (51,1) would lead to the same unique key as (5,11), namely 511.

Does anyone have a smart way to generate an integer from two integers, so that the generated integer is unique to a pair of initial integers?

Edit: After you come across an impressive amount of math, I realize that one detail that I had to include was the dimensions of the keys in question. In the original pair, the first key is currently 6 digits and is likely to remain at 7 digits for the life of the system; the second key has not yet received more than 20. Given these limitations, it seems that the problem is much less complicated.

Answer 1

Multiply one with a sufficiently high value

SELECT id1 * 1000000 + id2 

Or use text concatenation:

 SELECT CAST(CAST(id1 AS nvarchar(10)) + RIGHT('000000' + CAST(id2 AS nvarchar(10)), 6) AS int) 

Or skip the integer thing and separate the identifiers with something non-numeric:

 SELECT CAST(id1 AS nvarchar) + ':' + CAST(id2 AS nvarchar) 

Answer 2

You can mathematically prove that this is not possible if you want the resulting key to consist of 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 

Answer 3

This has already been discussed in sufficient detail (in the recursive statement, however, the output should consist of more bits than the individual inputs).

Match two integers with one unique and deterministic way

How to use two numbers as a card key

http://en.wikipedia.org/wiki/Cantor_pairing_function#Cantor_pairing_function

Answer 4

You can only do this if you have an upper bound for one of the keys. Let's say you have key1 and key2 , and up1 is the value that key1 will never reach, then you can combine these keys as follows:

 combined = key2 * up1 + key1; 

Even if the keys can theoretically grow unlimitedly, you can usually evaluate the practical value of the upper bound.

Answer 5

Both proposed solutions require some knowledge of the range of keys accepted.

To avoid this assumption, you can combine the numbers.

Key1 = ABC => Digits = A, B, C
Key2 = 123 => Digits = 1, 2, 3
Riffle(Key1, Key2) = A, 1, B, 2, C, 3

Zero-padding can be used when there are not enough numbers:

Key1 = 12345, Key2 = 1 => 1020304051

This method also generalizes for any number of keys.

Answer 6

As I like the theoretical side of your question (it’s really beautiful), and to contradict what many of the practical answers say, I would like to give an answer to the “mathematical” part of your tags :)

In fact, it is possible to match any two numbers (or virtually any series of numbers) with one number. This is called the Gödel number and was first published in 1931 by Kurt Gödel.

To give a quick example, with your question; let's say we have two variables v1 and v2. Then v3 = 2 ^v1 * 3 ^v2 will give a unique number. This number also uniquely identifies v1 and v2.

Of course, the resulting v3 number can grow undesirably quickly. Please just take this answer as an answer to the theoretical aspect in your question.

Answer 7

wrote them for mysql, they work great

CREATE FUNCTION pair (x unsigned BIGINT, unsigned BIGINT) RETURNS BIGINT unsigned DETERMINISTIC RETURN ((x + y) * (x + y + 1)) / 2 + y;

CREATE FUNCTION reversePairX (z BIGINT unsigned) RETURNS BIGINT without signature DETERMINISTIC RETURN (FLOOR ((- 1 + SQRT (1 + 8 * z)) / 2)) * ((FLOOR ((- 1 + SQRT (1 + 8 * z)) / 2)) + 3) / 2 - z;

CREATE FUNCTION reversePairY (z BIGINT unsigned) RETURNS BIGINT without signature DETERMINISTIC RETURN z - (FLOOR ((- 1 + SQRT (1 + 8 * z)) / 2)) * ((FLOOR ((- 1 + SQRT (1 + 8 * z)) / 2)) + 1) / 2;

Answer 8

At the risk of sounding witty:

 NewKey = fn(OldKey1, OldKey2) 

where fn () is a function that looks for a new key value for autostart from a column added to an existing table.

Obviously, two integer fields can have exponentially more values ​​than a single integer field.

Answer 9

Why don't you just use ROW_NUMBER () or IDENTITY (int, 1,1) to set a new identifier? Do they REALLY need to be in relation?