PrimaryKey vs Idkey

Question

Question

Neerav Verma · Mar 5, 2019

#Data Model #Indexing #SQL #Caché

Just wondering an Insight in the difference between these two indexes

IdKey / PrimaryKey
=================

Property Identifier As %Integer

Index Index1 on Identifier [Idkey]

Index Index2 on Identifier [PrimaryKey]

What's the difference?

1. If I don't have Index1 and only have Index2, then cache does still make its own id.
So how and why do I ever use the PrimaryKey. In Joins ??

Table1.Identifier = Table2.Identifier instead of Table1.Id = Table2.id ??
But I can still use Table1.Id = Table2.Id as cache still made one ID field

So where is PrimaryKey useful in cache?

2. If I declare Index1 , I am not able to have any Bitmap indexes [Cache throws an error on compilation saying I have an Idkey index]

Discussion (4)1

Log in or sign up to continue

Aviel Klausner · Mar 5, 2019

In short, the primary key is the value the application uses to identify a row in the table (for example in joins). The %ID (IDKEY) is the value the systems uses internally (the "row address") to identify a row in the table.

Thus the system always maintains an index on the primary key, so the application can use it, in addition to the "master map", which is an index on %ID.

Often the primary key is a value generated by the application, while the %ID is a system-generated integer.

Obviously the duality of having 2 different ways and indexes to identify rows might not necessarily be a good thing, and you can overcome that in 2 ways:

Use the application-generated primary-key value as the IDKEY. You can do that by identifying the primary key index in the class definition with both keywords PrimaryKey and IdKey (you can also do that from DDL if you correctly set the special flag for this purpose). This will make the primary key index be the table's "master map", i.e. the primary key will be used as the main internal address for the rows.
Do not use an application-generated primary key value, and instead use the system-generated %ID integer within the application as the application-used primary key (for example in joins).

Note that solution 1 above could be a problem, esp. if the primary key consists of more than one field or is not an integer, since the system-generated %ID lends itself better to more efficient processing, including usage of bitmaps.

So, depending on the nature of the application, sometimes it's better to have separate indexes for the application-generated primary key and the system-generated %ID.

8 0

score 2 · Answer 1 · 2019-03-05T15:32:29-05:00

Your IDKEY is your ID field, so it is a unique identifier that also becomes the subscript of the global you use to store your data. A Primary Key is any unique field (or combination of fields). Having it be primary key is the same as it being unique, as far as I know.

Changing your Identity property to have MINVAL=1 should make your class bitmap eligible. If that doesn't work, please contact the WRC.

score 2 · Answer 2 · 2019-03-05T15:47:28-05:00

1) Primary Key in Caché / IRIS is useful in that you can expose to third-party tools (typically through xDBC catalog queries) what the Primary Key is for your table. Other than that, it has no special meaning to us.

2) You can define bitmap indices using Index1 if you tweak the property definition of Identifier to tell the system the value is a positive integer:

Property Identifier As %Integer(MINVAL=1)

score 0 · Answer 3 · 2019-03-06T03:18:28-05:00

You already got the expert answers, but maybe I'd just add this cautious recommendation: You should look at the IDKEY index keyword as a means to publish the internal rowid through a different name besides its default "%ID" alias. Unless you're mapping a class to an existing global structure, there's not many reasons nowadays to want to override it beyond that, as you may jeopardize some storage and runtime efficiencies like index options (eg bitmaps & bitslices).

The primary key is what you as the schema designer decide to be the key for your table. If you don't choose one, we'll just default to that internal rowid for you (cf option 2 in Aviel's answer).