I have a vendor database that I want to use Change Data Tracking against but cannot because none of their tables have Primary Keys defined. The tables do have Unique Indexes but they are not defined as PK Constraints. It seems to me that making them Primary Keys would be a safe thing to do (i.e. will not interfere with or change the software operation) given that they are already Unique Indexes.

Can anyone tell me if my understanding is correct or if I am missing something?

  • Safe is kind of vague. Is it possible? Yeah, since the PK needs to be unique by definition. Is it a good idea? It depends. How many columns are you talking about and what data types are they? – LowlyDBA - John McCall Nov 17 '17 at 14:55
  • 3
    Assuming the columns don't allow NULLs, you should check with your vendor since they manage the schema. Changing the index to a PK will change error messages on dups that they might be looking for. – Dan Guzman Nov 17 '17 at 14:55
  • The unique indexes are all on a single Numeric(9,0) column set to AutoIncrement Identity. Thanks for the thought on the error returns possibly changing. Ill have to test on that a bit – Don McCall Nov 17 '17 at 15:04
  • 1
    To Dan's point, also make sure they're OK with you doing this and using change data tracking - you might be voiding support by modifying the database. – LowlyDBA - John McCall Nov 17 '17 at 15:09
  • 1
    @Vérace why? OP says all the tables have UNIQUE constraints, which they are not much different than PK constraints. – ypercubeᵀᴹ Nov 17 '17 at 15:40

No it's not safe.

From a query perspective it might look OK, but if you don't know how the application handles primary keys you shouldn't change that without checking with the vendor first.

You could break their error handling, their future update scripts etc.

Just to give you an example, during a future upgrade they may want to change some tables where you added CDC and they will receive an error. What does your contract say about support in that case?

If you are using a vendor database it's up to them to support it. If you go around making changes you are basically putting your system in a state they never tested so they would (rightfully so) probably tell you you have an unsupported configuration when things go wrong.

We don't know what application or vendor you are talking about, but if it doesn't support customizations you probably shouldn't customize the database without asking them first.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.