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.
+4
A:
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.
Aaron Alton
2009-06-15 20:50:00
@Aaron. We have a recursive reference between synonims of the same product.
Igor Zelaya
2009-06-15 21:07:46
@Igor: Then likely what you want is to store the product-name in a separate table, with a 1-to-many relationship between the product-info and product-names. Deferred Constaints Checking would be very helpful in cases where you want to swap or reorder a unique key, though (such as an *"Ordering"* column)
BlueRaja - Danny Pflughoeft
2010-03-19 19:36:53