Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    27

    Unanswered: How to extract "Referential Integrity"

    Hi,

    i'm working with a Oracle-DB where nobody can tell me about the ri. So i tried to crearte a script to get this information by my own. I've got:
    Code:
    select  c.OWNER cown,
            c.TABLE_NAME ctab,
            c.CONSTRAINT_NAME ccons,
            cc.COLUMN_NAME ccol,
            r.TABLE_NAME rtab,
            rc.COLUMN_NAME rcol,
            cc.POSITION
    from    all_constraints c, 
            all_constraints r, 
            all_cons_columns cc, 
            all_cons_columns rc
    where   c.CONSTRAINT_TYPE = 'R'
    and     c.OWNER not in ('SYS','SYSTEM')
    and     c.R_OWNER = r.OWNER
    and     c.R_CONSTRAINT_NAME = r.CONSTRAINT_NAME
    and     c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
    and     c.OWNER = cc.OWNER
    and     r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
    and     r.OWNER = rc.OWNER
    and     cc.POSITION = rc.POSITION
    order   by c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME, cc.POSITION;
    But here's me end. I need to knwo in which order do i have to insert data and vice versa to delete data.


    Thanx

    Roland

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In most cases:
    Inserting: first insert master, then detail
    Deleting: first delete detail, then master

    In special cases: it doesn't matter. Those cases are discussed in chapter "deferring constraint checking"; read a little bit about it here.

    EDIT: Previous link was the first one returned by Google; as I had to go out of the office, I couldn't search for more suitable link in Oracle Documentation. So, here it is.
    Last edited by Littlefoot; 12-01-05 at 03:53.

  3. #3
    Join Date
    Jan 2005
    Posts
    27
    Hi,
    Quote Originally Posted by Littlefoot
    In most cases:
    Inserting: first insert master, then detail
    Deleting: first delete detail, then master
    i knwo, therefore i'm trying to bild a query to tell me the order in which table i have to insert first. The reverse order is the right one for deleting records.
    What i'm looking for is a query result like this:
    tab_a1
    tab_xx
    tab_99

    So i have to insert data first in "tab_a1", then in "tab_xx" and at last in "tab_99". But as you can see in my already posted query, that's not what i'm looking for. I'm looking for help to get this (or an other) query working like i want to.

    Thanx

    Roland

Posting Permissions

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