Replication (dbremote) being ran against a table with a 5 part composite Primary Key

[former_SQLA_member1694871]

So I think I know the answer Reg. LOL!

I'm working with a team that is running 12 SQL Anywhere 17 databases in their Linux server environment.

Build 17.0.11.7312

In a nutshell, they are wanting to use SQL Remote to pass all data changes from the production database, down to a QA and Test database running in Azure. See image attached.

So production replicating to both QA and Test. And, to add a caveat, we are only replicating in one direction. So production will replicate down to QA and Test. Also, QA and Test will only be receiving from Prod. So data will not be going back.

So in a nutshell, we set up blanket publications and subscriptions against all the tables.

One of these tables however has a 5 part composite Primary Key. And unfortunately when this was designed YEARS AGO, the developer didn't see this as problematic.

So after we had applied the publication and subscriptions, we started testing the database and we got the following error....

Could not execute statement. Cannot update primary key for table "deposit summary' involved in a publication SQLCODE=-780, ODBC 3 STATE="42S02" Line 1, column 1

So I start pondering what is going on and it dawns on me. If the primary key value changes and that composite key no longer exists in the other environment, replication would fail for that action.

So we keep doing some digging and we find the "prevent_article_pkey_update" option on the database.

When we turned this off, the update statement immediately started working.

The impression I have is this. What kind of problem are we making for ourselves by turning this option off? The thought I have is that if someone updates a row in the QA database or the Test database that matches the key in production, that update would fail.

But what if we left the option on within test and QA, but off on the production side?

Thinking through the process, I'm feeling like that key value would be gone again, and the replication of that information would fail.

Why anyone would allow to update a primary key just floors me. But it's used in their batch processes.

I'm just trying to get my arms wrapped around what kind of other gotchas they could run into by going this direction.

I'm looking for anyone else that has run into this situation.

Thanks everyone!

[former_SQLA_member1694871]

And I'm sorry. I'm running into an error attaching images to a post.

[Baron]

What do you mean with blanket publications? Have you also created publications on the down side (Test+QA).

I think you need to create the publication/s only on the production database, and so that even without disabling "prevent_article_pkey_update" it will be possible to change the PK on Test+QA.

But of cource, only with uniformly built (and filled) PKs is guaranteed to have consistent data.

[former_SQLA_member1694871]

By blanket publications/subscriptions, basically what I meant is we did a select all against all the tables and added everything to replication. We didn't do anything crazy like "Only these two columns out of a five column table".

So in our instance, we had one publication with subscriptions for each table that have an "All Columns" selection within it.

We're running up the flag pole that we may have the prevent_article_pkey_update set to off in production, but on within the replication databases. But honestly, I still think we're going to run into issues.

This is a design that a developer set up 20 years ago and didn't realize that you absolutely should NEVER update a primary key.