Curcular dependency

kkchan

Hi,
When I compare database, I have warning as below. What does it meant?

Severity: High
Title: Circular dependency for the table or view [dbo].[ItemSizes]

Thank you

Brian Donahue

Hi and thanks for asking. What this warning points out is that the synchronization may or may not succeed because it's not entirely clear which object should be modified first due to recriprocal dependencies between two or more SQL objects being synchronized.

The solution isn't entirely clear -- you can let it run and if it succeeds then everything is alright. If it fails, you may want to synchronize parts of the database at a time or save the script to disk and modify it by hand. There is a full explanation in a previous versions forum post.

This happens when you have foreign keys created in such a way that there is a circular reference. So that you will have a database that looks something like..

Table_A has a foreign key to Table_B
Table_B has a foreign key to Table_C
Table_C has a foreign key to Table_A

I'd be very wary of synchronising data in these circumstances and thoroughly review your database schema. Have a look at SQL Dependency Tracker (ok I would say that wouldn't I ) http://www.red-gate.com/products/SQL_De ... /index.htm. This will give you a visual representation of your database and may help you track down any issues you have.

kkchan

Hi,
I have tried to use dependencies tracker to my database. How could I know what cause my table "circular reference"? It shows my table used by another trigger/view/table. Thats it. But it doesn't show how which table cause the circular ref.

Please advice.

Thank you

Brian Donahue

It's probably going to require some manual investigation by scripting the tables including triggers and foreign keys and seeing how these tie the tables involved together.