Table has foreign keys to other unsynchronized tables

gknierim

I am comparing the data between 2 databases and I get 3 warnings concerning Foreign Key constraints. The 3 warnings concern 3 tables, of which none of them have foreign keys in them. The message is as follows:
The table [dbo].[States] has foreign keys to other unsynchronized tables, the database may be left in an inconistent state.

Now, my States table only has a PK and no foreign keys. However, I have another table (called Teams) that has a foreign key to the PK in my States table. Is this what it is trying to tell me? If so, then the message should indicate the table dbo].[Teams] has foreign keys...

Also, these warning also appear even though I have selected all of the tables so technically, it shouldn't be left in an inconsistent state if I've included them all. I guess I'm just looking for clarification to this message as I feel it is inaccurate and misleading unless someone can explain it to me. It makes me think that i don't have something selected when I really do.

Thanks,
Greg

richardjm

I think you may be right about the message being misleading I've got a test database here of tbl1->tbl2->tbl3->tbl4->tbl5 and if I choose to only synch 3 and 5 I get a warning like you describe about tbl5 having foreign keys but in fact it's the unmigrated tbl4 that references tbl5 that is the warning. We should probably re-word the message a bit.

As to why you are getting warnings even when you include all tables in the synchronization that is more worrying. Do some of your tables not require synchronization or something?

We're just putting the last few touches on version 6.1 so I'll see if I can re-word that message for the release.

Well spotted

gknierim

Dang...tried posting a reply and it error'd out and I didn't save my long reply - will try again.

Thanks Richard.

I am including all of the tables since I am comparing an existing database to a new blank database. All of the tables have records in them and all of them are selected. I do remember trying to sync just the table that I got the warning message about and it synced fine with no warnings. So in your test database, if you try syncing tbl5, it should not give a warning message (I would think).

I had upgraded to 6.0 and was getting the same warning messages as v5 so whatever it is doing has been doing it for a while maybe. I saw it once before (a while back) and I investigated the table it was complaining about and saw that it didn't have a foreign key so I just dismissed the warning message.

If you find anything out, please let me know.

Thanks,
Greg

richardjm

There aren't any new tables in the target database that don't exist in the source or something clever like that?

Can all of the tables be compared, i.e. have they all have comparison keys set correctly?

I'm loath to go into that code and change too much as it's rather delicate in there, I did introduce a bug in 6.0 that I've fixed for 6.1.

The perils of being a programmer.

gknierim

No new tables in the target that don't exist in the source. I basically created a new target database, did a SQL Compare and synced all of the objects and then did a Data Compare to sync the data. All of the tables could be compared and none were left out - I don't understand the comparison keys - how do you set those (or do you)?

Ditto!

richardjm

You have to set comparison keys if not all your tables have unique primary keys or indexes. As all of your tables have synchronized then that shouldn't be the problem.

Is there any chance you can send me a minimal backup of the database that reproduces the problem and I can see if I can track it down that way. Alternatively if you could send me a sql compare snapshot so I can re-create the schema I could populate it with test data using our new tool that's in-development SQL Data Generator .

richard.mitchell@red-gate.com

gknierim

OK on the comparison keys. All of my tables do have unique PKs and/or indexes.

I will see what I can do to send you a backup. It will probably be later this week though or the weekend. I will email it to you when I get it ready if that's ok.

Thanks,
Greg

richardjm

That's fine. I'm more concerned about why it would be giving a warning if all items are synchronized more than the wording at this point.