Integer as primary key

[sathish_kumar82]

Hi,

I am designing a new table and the keys in the table are a combination of 5 or 6 columns. Making a composite key of 5 or 6 columns does not seem to be a good idea. We would like to use a surrogate key as our primary key (either integer, guid, number range). The number of records in this table can go really big and we want to limit the size of the table. Also, it is performance centric. Integer seems to be the best option as guid occupies more space and using number range requires additional processing of creating the unique ids.

Since in HANA, I dont see a sequence option (like in other DBs), so the unique id gets generated automatically when an insert happens, we have to mention the number (i.e. sequence key) in the INSERT statement. The best way, I could see is, to get the max(number) from the table append 1 to the key. The problem I see in this approach is that, if the program is run parallely, there is a danger of multiple people trying to insert with the same key.

What is the best approach? Kindly suggest.

[Juwin]

SAP has designed Number ranges concept for this. You should be able to use this, even with parallel processing.

Thanks,

Juwin

[sathish_kumar82]

I am very much aware of number ranges / guids and we use them extensively. The table is already existing with an integer key. I am trying to figure out how to uniquely generately the number in case of concurrent users.

I am exploring the option of making the column an IDENTITY column in HANA or utilizing the sequence.NEXTVAL function of HANA. I will post a different question for more clarity on this.

[Juwin]

OK, if in case you don't succeed:

In order to use number ranges, you don't have to touch the existing table structure. You may find the last number from the table and set that as the current number for this number range and SAP will start generating new numbers from that one onwards.

Thanks

[matt]

I concur. Number ranges are designed for use in exactly this kind of scenario. Simple, quick and easy. Why reinvent the wheel?

[n_soni]

This message was moderated.

[sathish_kumar82]

I cannot use GUID / number range as primary key as this is an existing table and cannot change the key.

Please see this post and let me know the best option