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 12»»

Foreign Keys Expand / Collapse
Author
Message
Posted Wednesday, May 24, 2006 8:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 20, 2007 12:30 PM
Points: 24, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rlobo/foreignkeys.asp
Post #282433
Posted Tuesday, May 30, 2006 9:45 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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

Post #283765
Posted Wednesday, May 31, 2006 4:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 16, 2013 2:09 PM
Points: 273, Visits: 17
 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.



Post #283811
Posted Wednesday, May 31, 2006 6:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 20, 2007 12:30 PM
Points: 24, 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….

Post #283840
Posted Wednesday, May 31, 2006 12:30 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:05 AM
Points: 779, 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.
Post #284018
Posted Wednesday, May 31, 2006 12:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 12, 2009 10:14 AM
Points: 18, 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.

Post #284022
Posted Wednesday, May 31, 2006 3:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:25 PM
Points: 67, Visits: 165
"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.



Post #284064
Posted Wednesday, May 31, 2006 5:19 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 3, 2014 9:19 PM
Points: 505, Visits: 1,691

"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. 

Post #284088
Posted Thursday, June 1, 2006 3:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444

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!




Post #284151
Posted Thursday, June 1, 2006 8:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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

Post #284224
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse