Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    26

    Unanswered: identify constraints/dependencies

    Hi,

    My problem exists because I have to replace the data for several tables from identical tables in another db (both are same version)

    I can't empty the tables because of the constraints (PK, FK and Check.)

    The problem is that when I try to disable the constraints (using Enterprise Mgr) I get an error that says that dependencies exist.

    What is the easiest way to determine these dependencies and then temporarily remove them so I can disable the constraints and add this data?

    Once I add the new data, I'll want to enable the constraints again.

    Thanks in Advance,

    John

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's a script that will disable fkey constraints ... the 2nd will
    reenable the constraints ... If you have to delete the fkey constraints,
    that attached script will build you a script that will create the
    foreign keys (for a given table). It will spool an output file for you ... go to the bottom of the script and change the location of the spool file to match your environment .... if you delete the fkeys, RUN the
    attached script 1st !!!

    also be sure that you don't have any triggers enabled that you might
    not want to fire ...

    hth
    Gregg

    SELECT 'alter table '||table_name||' disable constraint '||constraint_name||';'
    from user_constraints
    where r_constraint_name = (select constraint_name from user_constraints
    where table_name = UPPER('&table_name') and constraint_type = 'P')
    and constraint_type = 'R';


    SELECT 'alter table '||table_name||' enable constraint '||constraint_name||';'
    from user_constraints
    where r_constraint_name = (select constraint_name from user_constraints
    where table_name = UPPER('&table_name') and constraint_type = 'P')
    and constraint_type = 'R';
    Attached Files Attached Files

  3. #3
    Join Date
    Oct 2003
    Posts
    26
    Gregg,

    Thanks for your assistance, and the scripts. Unfortunately, no rows were selected from user_constraints. However, the constraints show up in Oracle Enterprise Manager.

    I am still having a problem with dependencies associated with the Primary key. I would like to be able to disable that constraint, truncate the table, insert the new data and then enable the constraint once the new data is in.

    What dependencies are they talking about? An association to another table? Could it be the index on that table... Should I delete the index table, disable the PK and then recreate the index?

    John

    Originally posted by gbrabham
    Here's a script that will disable fkey constraints ... the 2nd will
    reenable the constraints ... If you have to delete the fkey constraints,
    that attached script will build you a script that will create the
    foreign keys (for a given table). It will spool an output file for you ... go to the bottom of the script and change the location of the spool file to match your environment .... if you delete the fkeys, RUN the
    attached script 1st !!!

    also be sure that you don't have any triggers enabled that you might
    not want to fire ...

    hth
    Gregg

    SELECT 'alter table '||table_name||' disable constraint '||constraint_name||';'
    from user_constraints
    where r_constraint_name = (select constraint_name from user_constraints
    where table_name = UPPER('&table_name') and constraint_type = 'P')
    and constraint_type = 'R';


    SELECT 'alter table '||table_name||' enable constraint '||constraint_name||';'
    from user_constraints
    where r_constraint_name = (select constraint_name from user_constraints
    where table_name = UPPER('&table_name') and constraint_type = 'P')
    and constraint_type = 'R';

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Instead of USER_CONSTRAINTS use ALL_CONSTRAINTS and see if that shows anything up...

    HTH
    Gregg

  5. #5
    Join Date
    Oct 2003
    Posts
    26
    Gregg,

    I should have guessed that one! I modified the script so that it affected only the table I was interested in. When I ran it in SQL Plus, it listed the constraints (one PK and two FK) as disabled. I then committed the transaction.

    When I went to Enterprise Manager, it didn't display them as disabled. I tried to add a duplicate entry to test it and it said that the constraint existed (was enabled). When I went to Enterprise Manager and tried to disable it, I got the dependencies notification.

    I found out that 7 dependencies exist (2 tables, 1 User, 2 tablespaces, 1 profile and 1 datafile), but I am not sure how to disable them (or if I want to). I went to one of the tables and looked at the dependencies associated with it and found 137!

    All I want to do is replace data in a table or two (and all tables that they reference). The data will come from an exact duplicate db scheme. However, replacing the entire db is not an option.

    Should I be taking a different approach?

    Thanks in advance,

    John


    Originally posted by gbrabham
    Instead of USER_CONSTRAINTS use ALL_CONSTRAINTS and see if that shows anything up...

    HTH
    Gregg

Posting Permissions

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