Without knowning the entities represented by the tables, no answer can be given for why it's wrong, or even that it is wrong, although it probably is. If you tell us what the tables represent, it should be possible to suggest an easier method to model the relationship.

As Ron says, it's not necessarily wrong from a logical perspective. In fact, relationships of this kind which are mandatory in both directions are perfectly common in data modelling and in business requirements - it's just that SQL Server is a poor tool for implementing such rules.

For practical reasons it's usually necessary to compromise in some way. For example you could populate the tables with some initial data before enabling the constraint(s) or you could remove one of the constraints altogether.

David Portas (5/28/2010)As Ron says, it's not necessarily wrong from a logical perspective. In fact, relationships of this kind which are mandatory in both directions are perfectly common in data modelling and in business requirements - it's just that SQL Server is a poor tool for implementing such rules.

For practical reasons it's usually necessary to compromise in some way. For example you could populate the tables with some initial data before enabling the constraint(s) or you could remove one of the constraints altogether.

Any specific example? There might be but not seen any situation yet for circular relationships.

Any specific example? There might be but not seen any situation yet for circular relationships.

I expect you have come across relationships that ought to be mandatory in both directions. It's just that SQL makes it hard or impossible to implement them declaratively and so database designers have got into the habit of ignoring them or having them implemented in application code. Take almost any example of a multi-table relationship that includes the words "at least" or "exactly": Order must have at least one Order Item; every Employee must be assigned to exactly one Department and every Department must contain at least one Employee; every Branch requires exactly one Branch Manager.

OK. Let me give an example. I have one tblEmployee table with EmployeeID as Primary Key and another column DepartmentID.

Another tblDepartment with DepartmentID as Primary Key and another column ManagerID as Foreign Key from the Employee table.

The column DepartmentID in tblEmployee is the foreign Key from the tblDepartment.

Now the above mentioned simple design is implementable and logical. If not, plz let me know. I am expecting many employees against one department. But One department will have only one manager and his ID is refered in the tblDepartment (this will be done by implemented logic).

Secondly, you are right, the logic to keep the tables related two ways should be coded properly. But still, the data in both tables is insertable independently (keeping the Foreign key column NULL).

I don't know about wrong or not but it's a different example from the ones I had in mind.

Take a Manager table and a Department table (obviously manager may not be the same thing as employee - not all employees are managers). Now enforce the rule that each department has one manager and each manager has one department.

This is a special case of a 1-N relationship where N=1. In general most 1-N relationships are not easily supported in SQL unless N is allowed to be zero. That's because SQL FOREIGN KEY constraints are always optional on the referencing side of the relationship, ie the "parent" row must exist but a referencing row does not have to exist.