using foreign keys as part of primary key
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: using foreign keys as part of primary key

Hybrid View

  1. #1
    Join Date
    Jan 2002
    Posts
    12

    Question

    What are the pros and cons, performance-wise about using the primary key of the parent table as part of the primary key in the child tables v.s. just having it as a foreign key in the child tables ?? And if the keys are all surrogate keys, how many levels down can I migrate that first primary key ??

  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    It is hard to measure the effect on performance without more information.

    If you design to 3NF then the data should depend on the key, the whole key and nothing but the key (so help me Codd).

    If you are denormalizing, maybe you can just combine the parent and child tables.
    David Knight
    OCP DBA 8i, 9i, 10g

  3. #3
    Join Date
    Apr 2002
    Posts
    14
    One advantage is that it could save you having to create an additional index (in addition to surrogate key index) on the child fk columns if these are ever used in select criteria (which they often are as you typically want children that match some criteria for some particular parent).

    Pushing keys down into child tables is a real help if you need to query something way down a hierachy of parent child tables when the key of the grandparent/great grand parent table is needed in the query as it saves having to join thru all the intermediate parent tables. However wide keys effect the efficiency of the index. I've seen dramatic improvements on queries with 5 tables in parent-child configuration being adapted FROM surrogate keys to cascaded primary keys where the final child table had ~10 million rows. The key was numeric and only one column was introduced per level. If the key columns are "wide" (long character strings etc.) then this will tip the balance away from cascaded keys.

    Andy

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    ur desision may be depend from:
    1 -- u had to declare FK on child table
    2 -- u didn't declare FK constraint, and use "logical FK constraints" on application level.
    ------------------------------------------------------------------
    in first case better has
    -- 2 indexes on child table (PK index and FK index)
    in second case may be (but not always) better has
    -- composit index that has first fields are equal filelds from PK of parent table.
    ------------------------------------------------------------------
    Problem with perfomance on FK are
    -- locks on chaid table(s) when u update/delete rows from parent table.
    if u haven't any indexes on FK fields of the child table(s) and declare FK constraint
    oracle have to inmpement locks to all rows from child table
    (see Tom Kyte "deadlocks" and "indexes on fk")

    about 1 or 2 indexes on child tabe:
    if u have PK constraint, that not include any fields from FK constraint
    and u going to add FK fiels to composit of PK index u:
    -- if u place FK fields last, then oracle probably cant use this index
    with update/delete operation on a paret table.
    -- if u place FK fields first, then ur PK B+ index will be not so good
    balanced, as with PK fields only.

    This is reason why i gess to have 2 indexes.







  5. #5
    Join Date
    Jan 2002
    Posts
    12
    I see another aspect of using the foreign key as part of the primary, and that is utilizing key compression for the child table primary key. Is there much overhead during the key generation utilizing key compression versus not ??

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    U going to use compession or not it is doesn't metter.
    As i said before: MAIN PROBLEM IS LOCKS OF ROWS FROM CHILD TABLE
    WHEN ORACLE UPDATE/INSERT ROW(S) FROM PARENT TABLE.

    I try to explain situation again:
    1. PK always use B+ tree index.
    2. this type of index consists from banch and leaf nodes.

    3. if u place FK fields last then:
    ================================
    * First levels of tree:
    * keep information from pk field of child table only!
    -----------------------------------------
    * Last levels of tree
    * keep information from FK field of child table (or pk fields of parent table)
    ================================
    ORACLE CAN'T USE THIS INDEX FOR LOCK ROWS ON THE CHILD TABLE
    WHEN IT UPDATE/DELETE ROW(S) IN PARENT TABLE.
    ORACLE HAVE TO LOCK !!!ALL ROWS OF CHILD TABLE.

    4. if u place FK fields first THEN:
    ================================
    * First levels of tree:
    * keep information from FK field of child table
    -----------------------------------------
    * Last levels of tree
    * keep information from pk field of child table
    ================================
    oracle can use trhis index for lock,
    but ORACKE HAVE TO MAKE ADDITION READS FOR FOR GET
    EACH "CLEAR" PK OF A CHILD TABLE.



    [Edited by Shestakov on 07-18-2002 at 01:47 PM]

Posting Permissions

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



Click Here to Expand Forum to Full Width