DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: FK without index

  1. #1
    Join Date
    Aug 2000
    Posts
    194
    When a child table doesn't have its FK indexed, whether the lock will affect only the parent row or the whole parent table?

    Suppose I have the following tables

    PARENT ( colp number not null primary key,
    col2 number );

    CHILD ( COLc number not null Primary Key,
    COLp number,
    COLx number,
    constraint fk_colp foreign key (colp) references PARENT(colp)
    ) ;

    Assume that I do not have index defined on "colp" of CHILD.

    Now when I delete/insert/update a record from CHILD table, what kind of Lock it acquires on the parent table?.

    Will it acquire any lock, if the parent row is not at all afected?
    (say insert or update on colx)

    If it acquires any lock for any of the DML, will it just Lock the Parent record or it acquires a lock on the entire table?

    What is the case when the operation is on the PARENT table?

    Note: I went thru many of the documents (metalink) and couldn't decide whether the lock is just on the parent records or on the entire table?

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    What version of Oracle?

    The behavior of this 'situation' (bug, in my humble opinion) is different in different versions, AFAIK.

    - Chris

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Well, I gotta run. Here is a link for the behaviour under 8i:

    [url]http://oradoc.photo.net/ora8doc/DOC/server803/A54642_01/ch6.htm#1574[/url]

    The behaviour is that the entire child table will be locked when you delete a record from the parent or update the key field in the parent.

    Unfortunately, what you are describing sounds like the behaviour under 7.x, where the parent table gets locked. Unfortunately, I don't remember the details under 7.x and don't have a reference handy.

    Good Luck,

    - Chris

  4. #4
    Join Date
    Aug 2000
    Posts
    194
    Before I go thru the link, the version of my Oracle is 8.1.6

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Then the link I gave you is correct, as well as the description I gave you. The questions you were asking reflected the behaviour under 7.x. As I said, it changed. I got caught by the same thing when I switched projects and, hence, versions last year.

    The link I gave you is to a mirror site of the actual Oracle docs, you can find the same thing at the actual OTN site by serching for "Concurrency Control, Indexes, and Foreign Keys". This is Oracle's actual explanation of the scenario.

    Also, here is a quick script that *should* find any non-indexed FK columns for you. Mind you, I'm about a week away from re-visiting this entire issue myself, so if there is a flaw in my script, please let me know.

    SELECT
    ---*
    FROM
    ---ALL_CONS_COLUMNS---ACC
    WHERE
    ---EXISTS (
    ------------SELECT
    ---------------1
    ------------FROM
    ---------------ALL_CONSTRAINTS---AC
    ------------WHERE
    ---------------AC.CONSTRAINT_TYPE---=---'R'------------------AND
    ---------------AC.OWNER---------------=---(owner)------------AND
    ---------------AC.CONSTRAINT_NAME---=---ACC.CONSTRAINT_NAME
    ---------)---AND
    ---NOT EXISTS (
    ------------SELECT
    ---------------1
    ------------FROM
    ---------------ALL_IND_COLUMNS---AIC
    ------------WHERE
    ---------------AIC.INDEX_OWNER------=---(owner)------------AND
    ---------------AIC.TABLE_NAME---------=---ACC.TABLE_NAME---------AND
    ---------------AIC.COLUMN_NAME------=---ACC.COLUMN_NAME------AND
    ---------------AIC.COLUMN_POSITION---=---ACC.POSITION
    ------------)

    Just replace '---' with ' ' or a tab char. Also, replace (owner) with the name of your schema owner.

    Good Luck,

    - Chris

    [Edited by chrisrlong on 02-16-2001 at 10:27 AM]

  6. #6
    Join Date
    Aug 2000
    Posts
    194
    Chris:

    Thank you very much for your response. The link is the exact thing I was looking for.

    Regards,
    Prince.

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