Composite primary key

Question

Question

Muhammad Waseem · Jun 29, 2021

#Caché

Hi,

How can I define composite primary key in Cache?

Thanks

Product version: IRIS 2020.1

Discussion (6)4

Log in or sign up to continue

Arturo Masero · Jun 29, 2021

Oh, I was assuming you are using the caché Studio as your IDE. If so, it's under Class > Add > Index...

If you are using VS Code, Eclipse or other, I can't tell you, but the syntax I wrote works fine.

For more reference you have the online documentation, “Index Definitions.”

0 0

score 1 · Answer 1 · 2021-06-29T05:22:18-04:00

Hi,

The 'New Index' wizard allows you to add various properties to the index. Or, manually, you could have something like this:

Index MyIndex On (AProperty, MySecondProperty) [ IdKey ];

score 0 · Answer 2 · 2021-06-29T07:00:54-04:00

Muhammad Waseem · Jun 29, 2021

Thanks @Arturo Masero
Where is 'New Index' option?

0 0

score 2 · Answer 3 · 2021-06-29T16:56:02-04:00

The first question you should ask is: why do I need a composite primary key?
In general, in Cache/IRIS you don't want to mess with the default key provided by %Persistent storage.
In 99% of the cases, a unique index would be sufficient.

score 1 · Answer 4 · 2021-07-06T15:52:48-04:00

I agree with Herman - to a point. Leave the IDKEY index alone and just accept the system assigned idkey. In most cases this is the best policy. But - there are potentially several "candidate keys", all can be defined - or not, but one can be chosen to be the "primary key". In that case, define the key not as unique but as "primarykey". Some tooling works better with a defined primary key. It does not interfere with the IDKEY unless it is also defined as the "idkey".

score 0 · Answer 5 · 2021-07-07T07:50:26-04:00

One note - many use cases that call for a composite primary key in a typical boring database would be good use cases for a parent-child relationship in IRIS. This provides nicer features from an object/SQL perspective (e.g., using arrow syntax / implicit joins instead of explicit joins on the composite key fields). See: https://docs.intersystems.com/irisforhealthlatest/csp/docbook/Doc.View.c...