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


Foreign Keys


Foreign Keys

Author
Message
Ravi Prashanth Lobo-275382
Ravi Prashanth Lobo-275382
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rlobo/foreignkeys.asp
Mike C
Mike C
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10578 Visits: 1173

You got my vote In that Celko quote, I think he was specifically referring to a Primary Key.


geovar
geovar
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 18
Interesting article. Most of things are well known, but here they are summarized and there are also some interesting ideas.

The most curious for me was this one:


Recursive

I can not imagine any practical use of below code, but theoretically it is possible.

CREATE TABLE master( pkey int PRIMARY KEY FOREIGN KEY (pkey) REFERENCES master(pkey))

It really does nothing. I hoped it will prevent update of PK and deleting a record, 
so I tried it, but unfortunately it doesn't prevent either PK update nor record deleting.




Ravi Prashanth Lobo-275382
Ravi Prashanth Lobo-275382
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 1

You got me there!

Yes, Celko is referring to primary keys.

That was my favorite quote. I wanted to have it in my first article. Hope it will not harm someone….


Aaron Ingold
Aaron Ingold
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4395 Visits: 222
Recently I came across a highly transactional DB with no FK's! The data integrity was managed by the front-end application.

Unfortunately this is not uncommon in database / application design. I can't tell you the number of vendor-supplied applications I've looked at that don't make use of basic database features such as keys, indexes or constraints. Not to offend anyone, but it usually happens when you've got front-end developers driving the whole thing without a good DBA adding input. When I came to my current position, the developers were running the show and I had to slowly bring them over to agreeing to use things like stored procedures, constraints, keys and db-side functions.

Also, while there are many things I have fundimental disagreements with Celko on, I do like that quote as well.

Good article.
Jason1972
Jason1972
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 167

I really liked the script that will traverse the FK hierarchy. However, it looks like it will only work as long as there is no recursiveness - if a table has a FK within it to itself, the code will loop forever trying to traverse the same table over and over again.


SL10
SL10
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 179
"Recently I came across a highly transactional DB with no FK's! The data integrity was managed by the front-end application."

Another reason for this happening is that vendors try to hide proprietary information architecture. No referential integrity, combined with generic object names and procedures/function definitions created on one line. (Great Pains first comes to my mind.) With setup like that, it is really painful to "untagle" relationships at the backend.



Fal
Fal
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: 1025 Visits: 1813

"Recently I came across a highly transactional DB with no FK's! The data integrity was managed by the front-end application."

I've seen this also. The ERP system was designed to be "generic" - any database platform, on any OS, for any customer. As customers all have differing requriements, an FK for one might be detrimental to another, so all were left off.

And then, as the "customer", we paid a fortune getting the application customised to check the data integrity we required. A nice little earner, I must say.


Ian Yates
Ian Yates
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8028 Visits: 445

Good article - I too have come across some rather powerful systems with no FKs. Their excuse was that when data loading they could do it in any order. I might send them your quick and easy code to disable and enable all constraints.

Hope this article influences some folk to ensure they use ref integrity!





Mike C
Mike C
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10578 Visits: 1173

I think General Patton said: "Fixed fortifications are a monument man's stupidity." Same could be said for "generic" database applications.


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