Post reply

Foreign Keys

  • May 24, 2006 at 8:05 am

    #190699

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rlobo/foreignkeys.asp

  • May 30, 2006 at 9:45 pm

    #640826

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

  • May 31, 2006 at 4:07 am

    #640859

     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.

  • May 31, 2006 at 6:19 am

    #640880

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

  • May 31, 2006 at 12:30 pm

    #641022

    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.

  • May 31, 2006 at 12:48 pm

    #641026

    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.

  • May 31, 2006 at 3:42 pm

    #641058

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

  • May 31, 2006 at 5:19 pm

    #641073

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

  • June 1, 2006 at 3:45 am

    #641124

    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!

  • June 1, 2006 at 8:06 am

    #641186

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

  • June 1, 2006 at 11:39 am

    #641255 

     If you don’t have a key, you don’t have a table!     
                  - Joe Celko
    It is very nice article 
                which one can easly understand 
                without deep knowlege of MSSQL.
    Also basic concepts are explained very well !!!!!
     
     

  • June 2, 2006 at 1:04 pm

    #641517

    Good, terse explanation.

    The only issue I'd have with it is the use of the terminology "Parent" and "Child" to describe the relationship between the primary key and the foreign key(s) that reference it. In the best methodologies I've seen, the terms "parent" and "child" are reserved for unusual situations where you have things that depend for their existence on other things. An example of this might be a line on an invoice - the invoice must exist for the line to exist, do the invoice would be the "parent" and the line the "child". With dissimilar things, such as drivers and racecars, you'd have two distinct entities that were related by a foreign key, but neither would be the "parent" of the other.

    That's terminology, but it avoids confusion.

  • June 3, 2006 at 12:40 am

    #641555

    Fair comment from Roger.  Makes me think actually that the next step for the article would be many-many relationship as many people get that confused

  • June 14, 2006 at 7:16 am

    #643628

    As usual I am a day late and a dollar short, but heck was a good, clear, concise tutorial on foreign keys.

    Thanks for the effort you put into this ... and as suggested by some one else, would be nice if you took the challenge and attempted to 'explain' the many to many relationship.

    Again - thanks

     

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • July 20, 2006 at 6:25 am

    #650355

    Thank you all for your suggestions!

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply