Why are primary keys important?

[JC_Data_Wizard]

We noticed that starting in 2023.7 we are getting error messages in the loader status, if the Enforce Primary Key Constraint option isn't selected with Primary keys setup in the table.  Screenshot below. 

Someone on our team asked why we even need primary keys on the table. Outside of the deduplication it provides, I didn't really have a good answer. There isn't much on the importance of using primary keys in the documentation or the community. 

Outside of deduplication why do we need primary keys set up in the table? 

Thank you, 

John Cross

[RADSr]

"Need" is a strong word, but besides the mundane ( good practice to ID field usage ) the really important thing the key designation does is determine if an incremental load will append a row or update an existing row.

Additionally - might be an edge case for some users - the key designation needs to be there to allow the lookup() function to actually look something up.   It's ( IMHO ) a big pain-in-the-neck, but I've been bitten trying to figure out why a lookup isn't working only to discover I hadn't set the key(s) properly.    

[JC_Data_Wizard]

Thank for pointing that out Randy. Completely forgot about the incremental load and the lookup. I always set primary keys so I don't run into these issues often.

Are there any performance reasons? Why did Incorta in 2023.7 change to have the load show a failure when the primary key isn't set? Additional errors in the screenshot below. 

[RADSr]

I've asked if keys impact performance and the answer I got was "no" because of DDM.

That said we just went through a whole exercise changing security filters to use ID ( int ) fields instead of name ( string ) fields because we saw a fairly huge leap in performance due to the data types.    So there's that.

As for the errors I run between bemused and enraged at Incorta error messages so I can't be of any help there.

@JoeM  - can you share any further insight about the impact or lack of impact on performance when defining keys?    Or tag someone - I don't expect you to do *everything*  😉   

[JoeM]

Thanks for chiming in @RADSr . Tagging @mhelmy who could give more insight on this.