Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    Unanswered: delete and update properties for primary keys? (was "nother n00ber")

    All my texts talk about on delete and update properties for foreign keys. Do they work with primary keys, too?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You should probably read up on foreign keys, what they represent, and the details on update/delete issues. Primary keys are a completely different thing in this respect.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rbfree, could you do us a favour? please use a descriptive title for your threads
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2009
    Posts
    104

    yes on subject titles and thanks on reading advice

    Re. the reading advice, I've given no shortage of time to the reading, but as always with learning I have some kinks to work out. In this case, I worked it out after rethinking it. Nevertheless, I always appreciate a fresh perspective.

    And, certainly yes on the descriptive titles.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by rbfree
    All my texts talk about on delete and update properties for foreign keys. Do they work with primary keys, too?
    Actually, no foreign keys without primary keys (or at least, "alternate" keys)!
    That is, a foreign key on table C refers to a (sort of) primary key in table P.
    Otherwise said, a foreign key is a property of a pair of tables, (C,P); it "points" from table C to table P.
    (To complicate things even further, tables C and P might even be the same table ;-)

    Delete and update properties of a foreign key are thus properties of this "pointer" between the two tables C and P. They will limit some of the allowed actions on one of the tables, or "trigger" additional actions on one of the two tables.
    Which actions and what restrictions? That's exactly what the property tells us. E.g., an "update cascade" will have different restrictions and side-effects than a "delete restrict".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    regarding foreign key actions, perhaps this might help -- Relational Integrity -- r937.com

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2009
    Posts
    104

    resolved

    Rudy,

    Nice work on the article on relational integrity. Believe it or not, I did manage to get a primitive but working concept of relational integrity and even normalization via reading a few texts and pestering the kind people on this list.

    But then came the next learning phase which is to learn to use sql (ddl) to create these structures, these normalized (and then denormalized), interrelated tables. That created a few confusions that I expressed when posting this thread. But after a few mental revisions, I managed somehow to get a primitive but working understanding of the process (relating a formal conceptual design to a physical db using sql). And, with help by you and others, many texts, and some samples from the web, I've managed to create most of the ddl code I'll need.

    Now, I need to test it (and parts of it) which requires getting mysql established (installation and connection) on machine... which turns out to be more complex and contingent than I would have imagined.

    Sooo... when I get over that hump I'll probably be back with more questions. Meanwhile, thanks for all help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •