Composite primary key
Hi,
How can I define composite primary key in Cache?
Thanks
Comments
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 ];
Thanks @Arturo Masero
Where is 'New Index' option?
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.”
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.
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".
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…