<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Database Design
»
Relational Theory
»
Circular reference
Circular reference
Rate Topic
Display Mode
Topic Options
Author
Message
the_rutter
the_rutter
Posted Monday, September 06, 2010 7:05 PM
Forum 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
L' Eomot Inversé
L' Eomot Inversé
Posted Tuesday, September 07, 2010 3:19 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 5:48 PM
Points: 7,088,
Visits: 7,143
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #981409
Grant Fritchey
Grant Fritchey
Posted Tuesday, September 07, 2010 5:27 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 13,377,
Visits: 25,161
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #981473
L' Eomot Inversé
L' Eomot Inversé
Posted Tuesday, September 07, 2010 10:13 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 5:48 PM
Points: 7,088,
Visits: 7,143
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #981687
Grant Fritchey
Grant Fritchey
Posted Tuesday, September 07, 2010 10:17 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 13,377,
Visits: 25,161
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans
Product Evangelist for
Red Gate Software
Post #981691
Jeff Moden
Jeff Moden
Posted Tuesday, September 07, 2010 11:52 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906,
Visits: 26,790
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #981787
the_rutter
the_rutter
Posted Friday, September 10, 2010 4:17 AM
Forum 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
ChrisM@Work
ChrisM@Work
Posted Friday, September 10, 2010 5:02 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 4:04 AM
Points: 5,609,
Visits: 10,973
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
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
