SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Circular reference


Circular reference

Author
Message
the_rutter
the_rutter
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 12
Is it valid design for TableA to reference TableB (foreign key) and TableB to reference back to TableA (foreign key)? Or is this circular reference *always* bad?
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14400 Visits: 12213
the_rutter (9/6/2010)
Is it valid design for TableA to reference TableB (foreign key) and TableB to reference back to TableA (foreign key)? Or is this circular reference *always* bad?


In relational theory, it's fine and valid. And it can be useful. So not always bad.

In actual DBMSs it's often not allowed, or allowed but badly implemented with problems you have to programme around.

The relational calculus engine has to check foreign key constraints at transaction commit time if you want a system that allows this in its full generality - and many systems can't handle it properly because a foreign key constraint is checked on the individual statement, not on the whole transaction. Some systems therefore ban it alltogether, while others allow it but you have to programme round issues caused by doing the checks too early.

It's easy in principle to do it "properly" and mostly not terribly inefficient: one algorithm that works is check on each individual statement, if the check fails add the check (with enough information about the rows concerned to allow efficient processing) to a list to be re-evaluated on commit; on commit if any check on the list fails roll back and throw an error, otherwise allow the commit to proceed. In practise this isn't as easy as it sounds.

Tom

Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40427 Visits: 32665
I agree with Tom, but I'll go a little further, I wouldn't call it "bad", but I would do whatever I could to avoid that kind of circular reference. It has the potential for causing a great deal of problems, and you'll have to document the heck out of it for the next person who has to develop against or maintain the database, because if they're not very aware of what the issue, it'll bite them hard.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14400 Visits: 12213
Grant Fritchey (9/7/2010)
I agree with Tom, but I'll go a little further, I wouldn't call it "bad", but I would do whatever I could to avoid that kind of circular reference. It has the potential for causing a great deal of problems, and you'll have to document the heck out of it for the next person who has to develop against or maintain the database, because if they're not very aware of what the issue, it'll bite them hard.

We agree totally then - I should probably have made it clearer that although it's not always bad I think that (especially with most of the real DBMS software out there) it's usually bad.

Tom

Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40427 Visits: 32665
Tom.Thomson (9/7/2010)
Grant Fritchey (9/7/2010)
I agree with Tom, but I'll go a little further, I wouldn't call it "bad", but I would do whatever I could to avoid that kind of circular reference. It has the potential for causing a great deal of problems, and you'll have to document the heck out of it for the next person who has to develop against or maintain the database, because if they're not very aware of what the issue, it'll bite them hard.

We agree totally then - I should probably have made it clearer that although it's not always bad I think that (especially with most of the real DBMS software out there) it's usually bad.


Yeah, absolutely. I didn't think there was an iota of disagreement.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87130 Visits: 41113
the_rutter (9/6/2010)
Is it valid design for TableA to reference TableB (foreign key) and TableB to reference back to TableA (foreign key)? Or is this circular reference *always* bad?


Ok... your turn. Why would you need to do this in your case?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
the_rutter
the_rutter
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 12
I'm actually a .NET developer with little SQL experience. I noticed this circular reference in a new project I am working on and it raised alarms in my head. I have very vague memories of this sort of thing being bad from my school days...

I'd classify it as bad in this case as the people who created and maintained this system have left.

Thanks for all the help.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16428 Visits: 19554
the_rutter (9/10/2010)
I'm actually a .NET developer with little SQL experience. I noticed this circular reference in a new project I am working on and it raised alarms in my head. I have very vague memories of this sort of thing being bad from my school days...

I'd classify it as bad in this case as the people who created and maintained this system have left.

Thanks for all the help.


It would be interesting to see, if you have the time.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
roulupen
roulupen
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 0
Hi All,

I am also facing a Circular reference problem. But, not able to get any idea to fix it.

Scenario: I have 3 tables:

tblUser(ntUserID, vcUserName, vcPassword) - Keeps User Information
tblGroup( ntGroupID, ntGroupOwnerID, vcGroupName ) - Keeps Group Information
tblGroupUser( ntGroupUserID, ntGroupID, ntUserID ) - Keeps user who has been assigned into a group


Relationship:

tblUser - > tblGroup(ntGroupOwner) - > tblGroupUser(ntGroupID) <- tblUser(ntUserID)

So, here I am facing circular reference.

Any suggestion to avoid this
aaron.reese
aaron.reese
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1043 Visits: 902
Thats not a circular reference.

tblGroupUser is a cross-ref or bridge table to normalise the Many:Many relationship betwen users and groups. It allows one user to belong to many groups and for one group to have many users
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search