Sync db with new FK --> catch 22?
Patrik
Posts: 4
Hi!
I use SQL Compare (3.2.0.5) to sync a development DB to a test DB. When the development DB schema has foreign key changes the sync script will throw an exception. This is really easy to reproduce, but am I doing something wrong here or anybody know any workaround to this problem?
How to reproduce:
1. Create two an empty DB, DBOne and DBTwo.
2. Add a table, Table1, to DBOne with one column (PK), IDTable1 int.
3. Sync the 2 DB, making DBTwo the same as DBOne.
4. Add a new table in DBOne, Table2, with one column (PK), IDTable2 int.
5. Add a new column to Table1, IDTable2_FK, which will be a foreign key for for reference between Table1 and Table2.
6. Add the reference.
7. Sync the 2 DB, making DBTwo the same as DBOne. --> Exception like (the column names aren't exactly as the example steps above):
The following error message was returned from the SQL Server:
[1776] There are no primary or candidate keys in the referenced table 'dbo.Table2' that match the referencing column list in the foreign key 'FK_Table1_Table2'.
Could not create constraint. See previous errors.
The following SQL command caused the error:
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table2] FOREIGN KEY ([Table2_FK]) REFERENCES [dbo].[Table2] ([id])
I have tried to just choose one table to sync first before the other, but with the same poor result. Any suggestions?
Cheers!
// Patrik
I use SQL Compare (3.2.0.5) to sync a development DB to a test DB. When the development DB schema has foreign key changes the sync script will throw an exception. This is really easy to reproduce, but am I doing something wrong here or anybody know any workaround to this problem?
How to reproduce:
1. Create two an empty DB, DBOne and DBTwo.
2. Add a table, Table1, to DBOne with one column (PK), IDTable1 int.
3. Sync the 2 DB, making DBTwo the same as DBOne.
4. Add a new table in DBOne, Table2, with one column (PK), IDTable2 int.
5. Add a new column to Table1, IDTable2_FK, which will be a foreign key for for reference between Table1 and Table2.
6. Add the reference.
7. Sync the 2 DB, making DBTwo the same as DBOne. --> Exception like (the column names aren't exactly as the example steps above):
The following error message was returned from the SQL Server:
[1776] There are no primary or candidate keys in the referenced table 'dbo.Table2' that match the referencing column list in the foreign key 'FK_Table1_Table2'.
Could not create constraint. See previous errors.
The following SQL command caused the error:
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table2] FOREIGN KEY ([Table2_FK]) REFERENCES [dbo].[Table2] ([id])
I have tried to just choose one table to sync first before the other, but with the same poor result. Any suggestions?
Cheers!
// Patrik
This discussion has been closed.
Comments
Have you tried upgrading SQL Compare to 3.2.1.14? You can get this from Check for Updates, and it will be a free upgrade.
If I remember correctly, an earlier version of SQL Compare didn't drop the foreign key before the primary key and caused this error. It should be fixed now. If not, please let me know and we'll look into it again.
I have updated to the latest version but that was not really the reason the db sync didn't work the first time. I had to run sync a few times with different settings. First with the ignore FK, to create the missing tables and then with FK to add colums and references to the tables already existing.
It doesn't seems like SQL Compare can handle the scenario with new tables and references to existing one's. (or it's me not using the right settings :-))
Cheers!
// Patrik
Thanks for getting back. I was wondering if you could look into something for me... If you create a migration script for this table, by itself and including all dependent objects in SQL Compare, what does the script look like? Does it script the foreign and primary keys, just in the wrong order, or is is just missing the constraints?
Cheers!