Foreign Keys

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

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

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

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

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

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

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

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

  • 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!

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

  •  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 !!!!!
     
     
  • 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.

  • 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

  • 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]

  • 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