Sql Data Compare - Violation of PRIMARY KEY constraint

m_a_l_e_n

How do I get rid of the violation of primary key for a job that synchronizes the transactional database to a backup database?

Anu D

Hi Malennavata,

Thanks for your post.

Can you please let me know the exact error you are getting?

And also the version of SQL Data Compare you are using? (Help --> About SQL Data Compare)

Hiren

Hi redgate,

I'm getting the same error while synchronising DB from one server to another using SQL Data Compare 11.

Can anyone help me out with this?

Thanks,
Hiren

sam.blackburn

There are quite a few reasons why Data Compare could try to violate a primary key: for example, the product enforces uniqueness of your comparison key which defaults to the primary key, but you can set it to something else if you know how your rows should be distinguished from each other.

Some detailed information would be useful to help understand why Data Compare is not behaving as you expected.

PhilSebastien

Hi, I am getting the same error on a Data Compare script deployment.  It does not make sense because I am going from Bak to Live.  I did the Deployment this morning and some people entered data during that time even though I told them to not enter new data.  Now I just restored the Live database to start the process from scratch but again I get Violation of PRIMARY KEY constraint 'PK_tblScanPackBox'  What I need from you is if you can tell me in what folder the upgrade script is located.  I would like to run it manually in SQL and do only the tables that are needed for us to resume our work.  Where does Red Gate SQL Data Compare save the script it generates?

Dan_J

Hi there @PhilSebastien,

Please correct me if I am misunderstanding this in any way, but you could create a deployment script for review, before then using the SQL Data Compare GUI to deploy the changes, as shown in the screenshot below:
 
 

PhilSebastien

Thank you Dan_J.  Your tip is very helpful.  That is exactly what I would have needed back in December for my emergency data recovery issue.  Thank you for your help.  Your products are amazing, I have been using them for over 10 years on different projects at some of my customers that have the licenses.

Dan_J

Hi @PhilSebastien

No problem at all, I’m glad this was helpful! And thank you very much for positive feedback on our products! I’m delighted to hear you find them useful and continue to use them after so many years.