Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Circular reference Expand / Collapse
Author
Message
Posted Monday, September 6, 2010 7:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 10, 2010 4:14 AM
Points: 2, 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?
Post #981275
Posted Tuesday, September 7, 2010 3:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:57 AM
Points: 7,801, Visits: 9,551
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
Post #981409
Posted Tuesday, September 7, 2010 5:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 13,890, Visits: 28,285
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #981473
Posted Tuesday, September 7, 2010 10:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:57 AM
Points: 7,801, Visits: 9,551
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
Post #981687
Posted Tuesday, September 7, 2010 10:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 13,890, Visits: 28,285
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #981691
Posted Tuesday, September 7, 2010 11:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #981787
Posted Friday, September 10, 2010 4:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 10, 2010 4:14 AM
Points: 2, 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.
Post #983626
Posted Friday, September 10, 2010 5:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 6,811, Visits: 14,025
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
Post #983650
Posted Thursday, October 31, 2013 1:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 31, 2013 1:11 AM
Points: 1, 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







Post #1510044
Posted Wednesday, December 11, 2013 10:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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

Post #1521977
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse