deferred constraint checking

Question

Currently In our database design we have an circular reference between two entities. In other words, we have foreign keys in each table that reference each others primary key. In order to insert records in these tables we need to perform a deferred constraint checking. Is this possible in SQL Server 2008? I know Oracle DDL has special syntax for this.

Answer 1

There is no native way to do deferred constraint checking in SQL Server.

Likely your best option is to insert a NULL value into the foreign key column in the first table until the second record is inserted, and then as part of the same transaction, update the foreign key column in the first table.

I'm interested - what is the business reason for your circular reference? It's definitely an extraordinary requirement.

Also have a look at this thread, on the same topic.