Primary Key Required for Update Option

Question

Hi all,

I have an Alteryx workflow that reads data from one MySQL database, processes it, and then inserts to another MySQL database using the Output Data tool. I set the Output Options to Update; Insert if New, so that it will update the record, just in case it already exists. I kept getting this Primary Key Required for Update Option, so I defined the necessary primary keys. In MySQL, EACH primary key has to be unique. My keys are a combination, so I Googled and found that, to define multiple fields as unique, you have to add indexes, so I did. I don't think Alteryx recognizes this, though, as it is still looking for the keys.

Last time I addressed this, I added an update date which had a timestamp value, so that it was sure to be unique. Any other way I can do this without adding the timestamp?

I own the database it is written to so, I can make the necessary changes, if necessary.

Thank you.

chuckleswk · Answer

If you've already created the table and set primary keys (even with multiple columns) Alteryx will recognize those columns as having keys.

I built a test table using the following: SQL Primary Key and was able to get Alteryx to recognize both the ID and the lastname as a primary key:

RonGatmaitan · Answer

Thank you very much, @chuckleswk! Will have a look.

So no option of not adding a key, right?

chuckleswk · Answer

I have started creating a unique record key for every row of data that I'm loading. This is done by concatenating each field together and then converting it to a hash using MD5_UNICODE([Record_Key]). I have attached the flow that I often use for this.

The upside is that I can use this key to identify whether a field has changed in a record (if you have another record key such as a unique ID).

Record Key.yxmd