What are the challenges you face when working across database platforms? Take the survey
Options

Sync db with new FK --> catch 22?

PatrikPatrik Posts: 4
edited January 11, 2006 10:11AM in SQL Compare Previous Versions
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

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Patrik,

    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.
  • Options
    Hi Brian!

    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
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi 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?
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    There was a problem with an older version that affected a table with the primary key and foreign key on the same column and I was wondering if this still happens...
  • Options
    Thanx for the fast answer. I have been away on vacation and I haven't the time to check this right now but will do it in a couple of weeks. (Heavy mailbox :D)

    Cheers!
This discussion has been closed.