Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2006
    Posts
    56

    Unanswered: Controlling db constraints and triggers

    I need to write a script to export an entire database. Some of the data needs to be scrubbed, like SSN and so on that is confidential. So I cannot just used Oracle Export. So my idea is to make an sql script with all the needed insert commands.

    Problem is, there are key constraints, triggers and so on to protect data integrity. I need to turn them off temporarily to force feed the data. Are there 10g commands to do this?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    DIsable foreign key constraints using
    Code:
    ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
    Disable triggers using
    Code:
    ALTER TRIGGER trigger_name DISABLE;
    To enable those constraints/triggers back, just replace the DISABLE keyword with ENABLE.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Problem is, there are key constraints, triggers and so on to protect data integrity. I need to turn them off temporarily to force feed the data.
    HUH? I don't understand this "requirement".
    You should be simply SELECTing data & writing to a text file.
    How/where to triggers and/or key constraints get in your way & prevent you from doing SELECT & writing text file?
    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.

  4. #4
    Join Date
    Aug 2006
    Posts
    56
    Thanks

  5. #5
    Join Date
    Aug 2006
    Posts
    56
    Quote Originally Posted by anacedent
    >Problem is, there are key constraints, triggers and so on to protect data integrity. I need to turn them off temporarily to force feed the data.
    HUH? I don't understand this "requirement".
    You should be simply SELECTing data & writing to a text file.
    How/where to triggers and/or key constraints get in your way & prevent you from doing SELECT & writing text file?

    I need to import the data into another database. That is why I making insert statements.

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    So we're assuming here that you're exporting from Oracle and importing back into another Oracle database that has the same constraints/DDL/triggers. If this is the case then Littlefoot's anwer would be the correct one

  7. #7
    Join Date
    Aug 2006
    Posts
    56
    I am having problems with the disabling and enabling of constraints.


    When I execute this, there is no error. But when I start to do inserts, I get a constraint error.
    ALTER TABLE CUSTOM_RESOURCE DISABLE CONSTRAINT PK_CUSTOM_RESOURCE CASCADE ;

    When I go to turn the constraints back on, I also get an error on the fk key constriant:
    ALTER TABLE CUSTOM_RESOURCE ENABLE CONSTRAINT FK_CUSTOM_RESOURCE_RES_CODE

    Error report:
    SQL Error: ORA-02298: cannot validate (PVDEV2.FK_CUSTOM_RESOURCE_RES_CODE) - parent keys not found
    02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
    *Cause: an alter table validating constraint failed because the table has
    child records.
    *Action: Obvious

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    the problem is exactly what it says. You have filled a child table and NOT filled the parent table so that the foreign key can not find a matching key in the parent table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Aug 2006
    Posts
    56
    Well, I did turn the pk back on before turning the fk back on

    I did this first

    ALTER TABLE CUSTOM_RESOURCE ENABLE CONSTRAINT PK_CUSTOM_RESOURCE;


    Is there a way schema wide to turn off constraints?

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Not with a single statement.

    But you might create a file full of ALTER TABLE ... DISABLE CONSTRAINT statements and run it later. It can be done manually (open a text editor and write those statements one by one), or you might spool result of a SELECT statement which would create ALTER TABLE statements for you (query USER_CONSTRAINTS), such as
    Code:
    SELECT 'ALTER TABLE ' || table_name || 
           ' DISABLE CONSTRAINT ' || constraint_name || ';'
    FROM user_constraints;

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by mburke
    Well, I did turn the pk back on before turning the fk back on

    I did this first

    ALTER TABLE CUSTOM_RESOURCE ENABLE CONSTRAINT PK_CUSTOM_RESOURCE;


    Is there a way schema wide to turn off constraints?
    Your error has NOTHING to do with having the primary key turned on. It has to do with you having a row in a table that has a foreign key to another table that does NOT have the parent key. If you do not understand foreign keys please read up on them
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    Aug 2006
    Posts
    56
    I did try that. It reported that it turned off the constriants. But when I tried to do inserts, I got unique key errors anyway.

    When I turned all the constriants back on, all the fk constriants got the same error as above.

  13. #13
    Join Date
    Aug 2006
    Posts
    56
    Quote Originally Posted by beilstwh
    Your error has NOTHING to do with having the primary key turned on. It has to do with you having a row in a table that has a foreign key to another table that does NOT have the parent key. If you do not understand foreign keys please read up on them
    I see what you mean. I deleted the offending row.

    But I still get the same fk error if I do no inserts, and just turn the constraints off and back on again.

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I wouldn't say so.
    But I still get the same fk error if I ... just turn the constraints off and back on again.
    Oracle wouldn't allow you to have an enabled constraint and, in the same time, data that violates that constraint. No way.

    I'd rather say that this constraint already WAS disabled (but you didn't check it) and enabling it raised an error.

  15. #15
    Join Date
    Aug 2006
    Posts
    56
    Quote Originally Posted by Littlefoot
    I wouldn't say so. Oracle wouldn't allow you to have an enabled constraint and, in the same time, data that violates that constraint. No way.

    I'd rather say that this constraint already WAS disabled (but you didn't check it) and enabling it raised an error.

    I did delete the bad row. When I re-ran the enable statements I got the same error.

    It did this on all tables, including the tables I did no inserts.

Posting Permissions

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