Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Foreign Keys / Parent Tables

doofusboydoofusboy Member Posts: 256
Not sure how to explain this, but I'll give it a shot.

Need to populate 300+ Oracle tables, many of which have foreign keys, so I need to establish a sequence in which data is loaded into these tables so that the constraints are not violated.

Can anyone help with a SQL query that will help me identify the following?

1) name of foreign key constraint
2) name of table containing a foreign key
3) name of parent table referred to by the foreign key
4) name of column(s) in parent table referred to by the foreign key

From this info I', HOPING I can construct a proper load sequence.

Any other suggestions are certainly welcome.

Comments

  • infidelinfidel Member Posts: 2,900
    [b][red]This message was edited by infidel at 2003-8-20 7:29:31[/red][/b][hr]
    : Not sure how to explain this, but I'll give it a shot.
    :
    : Need to populate 300+ Oracle tables, many of which have foreign keys, so I need to establish a sequence in which data is loaded into these tables so that the constraints are not violated.
    :
    : Can anyone help with a SQL query that will help me identify the following?
    :
    : 1) name of foreign key constraint
    : 2) name of table containing a foreign key
    : 3) name of parent table referred to by the foreign key
    : 4) name of column(s) in parent table referred to by the foreign key
    :
    : From this info I', HOPING I can construct a proper load sequence.
    :
    : Any other suggestions are certainly welcome.

    Try selecting * from user_constraints where constraint_type = 'R'. You can also disable constraints while you load the tables then enable them when you're done (as long as none of your data causes a violation). I think you disable a constraint with an alter table statement.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]



  • doofusboydoofusboy Member Posts: 256
    : [b][red]This message was edited by infidel at 2003-8-20 7:29:31[/red][/b][hr]
    : : Not sure how to explain this, but I'll give it a shot.
    : :
    : : Need to populate 300+ Oracle tables, many of which have foreign keys, so I need to establish a sequence in which data is loaded into these tables so that the constraints are not violated.
    : :
    : : Can anyone help with a SQL query that will help me identify the following?
    : :
    : : 1) name of foreign key constraint
    : : 2) name of table containing a foreign key
    : : 3) name of parent table referred to by the foreign key
    : : 4) name of column(s) in parent table referred to by the foreign key
    : :
    : : From this info I', HOPING I can construct a proper load sequence.
    : :
    : : Any other suggestions are certainly welcome.
    :
    : Try selecting * from user_constraints where constraint_type = 'R'. You can also disable constraints while you load the tables then enable them when you're done (as long as none of your data causes a violation). I think you disable a constraint with an alter table statement.
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    :
    :
    :
    Thanks for your reply. Have been using the [b]user_cons_columns[/b] table in my query as this table contains all the info I need; just keep getting multiple matches returned. Disabling constraints is not an option with this particular client as referential integrity is of utmost importance.
  • infidelinfidel Member Posts: 2,900
    : Thanks for your reply. Have been using the [b]user_cons_columns[/b] table in my query as this table contains all the info I need; just keep getting multiple matches returned. Disabling constraints is not an option with this particular client as referential integrity is of utmost importance.

    Does select distinct help? Could you use user_constraints in a hierarchical query (using "connect by")? I've never done a query like that so I can't really say more.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • doofusboydoofusboy Member Posts: 256
    : : Thanks for your reply. Have been using the [b]user_cons_columns[/b] table in my query as this table contains all the info I need; just keep getting multiple matches returned. Disabling constraints is not an option with this particular client as referential integrity is of utmost importance.
    :
    : Does select distinct help? Could you use user_constraints in a hierarchical query (using "connect by")? I've never done a query like that so I can't really say more.
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    :
    Tried Distinct and still got multiples. Someone else mentioned 'hierarchial query' to me also, but they too weren't sure how that worked. I've been using the following which uses a self-join as all the info I need is contained in one table:

    SELECT A.CONSTRAINT_NAME AS FOREIGN_KEY, A.TABLE_NAME AS FOREIGN_TABLE, A.COLUMN_NAME AS FOREIGN_COLUMN, B.TABLE_NAME AS PARENT_TABLE, B.COLUMN_NAME AS PRIMARY_KEY
    FROM USER_CONS_COLUMNS A, USER_CONS_COLUMNS B
    WHERE A.CONSTRAINT_NAME IN <----- name of foreign key
    (SELECT CONSTRAINT_NAME
    FROM USER_CONS_COLUMNS
    WHERE CONSTRAINT_NAME LIKE '%FK%')
    AND A.TABLE_NAME IN <----- name of table containing foreign key
    (SELECT TABLE_NAME
    FROM USER_CONS_COLUMNS
    WHERE CONSTRAINT_NAME LIKE '%FK%')
    AND B.TABLE_NAME IN <----- name of parent table reference by FK
    (SELECT TABLE_NAME
    FROM USER_CONS_COLUMNS
    WHERE CONSTRAINT_NAME LIKE '%PK%')
    AND A.COLUMN_NAME = B.COLUMN_NAME
  • doofusboydoofusboy Member Posts: 256
    Just as an FYI, in case anyone ever needs it, solution was:

    SELECT
    CON.TABLE_NAME FK_TABLE,
    CON.CONSTRAINT_NAME,
    CON.CONSTRAINT_TYPE,
    CON.R_CONSTRAINT_NAME,
    COL.TABLE_NAME PK_TABLE,
    COL.COLUMN_NAME PK_COLUMN
    FROM
    USER_CONSTRAINTS CON,
    USER_CONS_COLUMNS COL
    WHERE
    CON.CONSTRAINT_TYPE = 'R' AND
    CON.R_CONSTRAINT_NAME = COL.CONSTRAINT_NAME
    ORDER BY
    CON.TABLE_NAME


Sign In or Register to comment.