foreign keys to other unsynchronized tables

albertvo

Can anyone give some insight into what this warning means?

The table [dbo].[MyTableName] has foreign keys to other unsynchronized tables, the database may be left in an inconsistent state.

This is a warning in the Data section of the script generated by SQL Packager.

Thanks
Albert

Brian Donahue

Hi Albert,

Foreign keys exist to ensure that data is relationally consistent, so if for instance you have an FK relationship between a customer and a purchase table, it would prevent you from inserting a purchase before the customer record is created for the purchaser.

SQL Packager may temporarily drop this foreign key, allowing you to completely violate the referential integrity. Therefore if you synchronize the purchase table and then exclude the customer table from synchronization, the data consistency enforced by the foreign key in the database being synchronized to is completely lost.

The warning is advising you to make sure that you check that both tables in a foreign key relationship are synchronized so that the data is consistent between the two tables.

albertvo

Thanks for the quick reply. I guess I was thrown off by the word "unsynchonized" because i associated that with synchronizing two existing databases.

Brian Donahue

Hi Albert,

It's a side-effect of re-using SQL Data Compare's components in Packager. The dll has no concept of creating a database, only comparing two existing ones -- and in Packager's case it still does this: it synchronizes on database to another 'empty' database.

Nevertheless, the warning is still applicable to a new database package.

rwyarger

Brian Donahue wrote:

Hi Albert,

It's a side-effect of re-using SQL Data Compare's components in Packager. The dll has no concept of creating a database, only comparing two existing ones -- and in Packager's case it still does this: it synchronizes on database to another 'empty' database.

Nevertheless, the warning is still applicable to a new database package.

Hello Brian -

I too have run into this problem, and have seen it whether or not I have my foreign keys enabled or disabled by the tool (prior to making the schema and data files).

I currently have about a 43mb data file along with a 1.5mb schema, and have not been able to recreate my database with either the option to create a .NET exe, nor with the SQL Management Studio, nor from the command line with sqlcmd.

Is there something missing here? I've attempted about a half dozen creations of the 2 files now, and regardless - I still am unable to recreate my database.

Brian Donahue

Hi,

Sorry, I don't understand. This warning message should not prevent you from being able to create a database. Is this a different problem entirely?

rwyarger

Brian Donahue wrote:

Hi,

Sorry, I don't understand. This warning message should not prevent you from being able to create a database. Is this a different problem entirely?

I think I may need a different set of your tools to do what it is I need to do.

It seems that SQL Packager is not going to fit the bill for my needs (because of circular dependencies between tables).

Thus - I am going to check out SQL Compare and SQL Data Compare for the version control and db reconstruction that my company needs to do.

Thanks and sorry for all the posts on this.