Output data to oracle database table having multiple primary keys

Question

Hello community :D

Is it possible to use alteryx to ouput data from workflow to a table having multiple primary keys? I am using the update , insert if new in the output tool. But i have error "violation of unique constraint" for the table primary key 🔑

Thanks

Sebastiaandb · Answer

Hi @Youssef_BenSalem ,

We're using Oracle a lot! So i can probably help you out with this issue.

For what i'm aware of, you can only have 1 primary key in a table but you can have multiple columns that together make up your primary key.

The error "Violation of unique constaint" happens 99% of the time whenever you try to insert a key that already exists in your table.

A way of solving this for you would be to make 2 streams out:

1 - Append Existing (only new inserts) -> Feed it with all fields including new pk's.

2- Update - Error on update failure -> Only feed it with the primary key fields and the fields that changed (in most cases you use this to give records a datetimestamp/close records).

Let me know if it works :-).

Greetings,

Seb