This is a pretty common problem but I haven't yet found the exact question and answer I'm looking for.
I have one table that has a FK pointing to its own PK, to enable an arbitrarily deep hierarchy, like the classic tblEmployee that has a column Manager
that is a FK with the PK tblEmployee.EmployeeID.
Let's say in my app, the user
- Creates new employees Alice and Dave, with no manager because they're the CEO and President. So
tblEmployee.Manager
is NULL for those two records.
- Create new employee Bob, with Alice as manager. Then create Charles with Bob as his manager. Their Manager fields contain the Primary Key value of another record in
tblEmployee
.
- Edit employee record for Alice, meaning to assign Dave has her manager (which would be fine) but accidentally set Alice's manager to be Charles, who is two levels down from Alice in the tree.
Now the table is in a circular reference instead of a proper tree.
What is the best way to make sure that Step 3 cannot be done in an application? I just need to make sure that it will refuse to do that last SQL update, and instead show some error message.
I'm not picky about whether it's a database constraint in SQL Server (has to work in 2008 or 2012) or with some kind of validation routine in the business logic layer of my C# app.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…