Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Unanswered: Removing Data with Referential Integrity

    Am preparing to move data from Training environment to Production environment. Client has chosen to 'scrub' the data in it's training environment by Importing it to Excel spreadsheets where it can be easily removed, sorted, etc. A macro was written to take the surviving data and place it into SQL Insert statements to move it into the Production database.

    However, before the Inserts are run, client wants to make sure all tables are cleared of any data entered via the vendor's installation scripts. The plan is to remove all constraints, truncate the tables, add back the constraints and then load the data.

    The question is, "Is the order in which constraints are removed / added back important with regard to referential integrity?"

    Any assistance sincerely appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > "Is the order in which constraints are removed / added back important with regard to referential integrity?"
    No, not really.
    The constraints do not need to be dropped.
    1) disable constraints
    2) truncate tables
    3) enable constraints
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    24
    Thanks for the quick reply 'anacedent'. But whether disabled/enabled or removed/rebuilt, the question remains - is the ORDER in which this is done important with regard to referrential integrity?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you try to simply disable a primary key that is referenced by foreign keys you will get an error:
    PHP Code:
    SQLalter table dept disable constraint pk_dept
      2  
    /
    alter table dept disable constraint pk_dept
    *
    ERROR at line 1:
    ORA-02297cannot disable constraint (TANDREWS.PK_DEPT) - dependencies exist 
    You could disable the foreign keys first, but more easily you could do this:
    PHP Code:
    SQLalter table dept disable constraint pk_dept cascade
      2  
    /

    Table altered
    Now the foreign keys that referenced this primary key have also been disabled.

  5. #5
    Join Date
    Jul 2003
    Posts
    24
    Thanks for your reply 'andrewst'.

    Just to clarify that I'm reading this correctly, are you saying that using 'cascade' eliminates the need to be concerned about the order in which constraints are disabled?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by doofusboy
    Thanks for your reply 'andrewst'.

    Just to clarify that I'm reading this correctly, are you saying that using 'cascade' eliminates the need to be concerned about the order in which constraints are disabled?
    Yes it does. All you need to do is disable all the primary and unique constraints (in any order) with cascade, and all foreign keys will be disabled automatically.

  7. #7
    Join Date
    Jul 2003
    Posts
    24
    Excellent. Thanks very much.

    One last question [sorry if I'm being a pest]. When re-enabling the constraints after I do my truncation, do I need to specify all constraints [including the foreign keys] and worry about the order in which the constraints are enabled?

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes. You must enable each primary/unique constraints before the foreign keys that reference it. Easiest way would be to enable all primary/unique keys, then enable all foreign keys.

  9. #9
    Join Date
    Jul 2003
    Posts
    24

    Thumbs up

    Once again, thanks very much. You've been a big help !

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    to delete the data you don't need to disable the PKs, just the FKs.

    One benefit of leaving the PKs enabled is that I always like them there to double-check my loading correct data. If you load ALL your data and then find out you have duplicates everywhere it is going to be a pain in the ass to figure out what tables have what duplicates.

    disable all FKs owned by user:
    PHP Code:
    set echo off
    set heading off
    set feedback off
    set linesize 130

    prompt
    prompt Alter All Foreign Keys Disabled 
    for Connected User
    prompt

    set term off
    spool fk_off
    .tmp

    prompt set 
    echo off
    prompt set feedback off

    select 
    'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'
      
    from user_constraints
      where constraint_type 
    'R'
      
    and status 'ENABLED';

    spool off
    set term off

    @fk_off.tmp
    quit 
    enable all constraints after you delete the data and re-load the data:
    PHP Code:
    set echo off
    set heading off
    set feedback off
    set linesize 130

    prompt
    prompt Alter All Foreign Keys Enabled 
    for Connected User
    prompt

    set term off
    spool fk_on
    .tmp

    prompt set 
    echo off
    prompt set feedback off
    prompt set time off timming off

    select 
    'alter table '||owner||'.'||table_name||' enable constraint '||constraint_name||';'
      
    from user_constraints
      where constraint_type 
    'R'
      
    and status != 'ENABLED';

    spool off
    set term off

    @fk_on.tmp
    quit 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Not sure I like the name of your disable script!

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296

    Talking

    lol!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    Classic naming scheme for you scripts Duck !!!!

Posting Permissions

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