3 Replies Latest reply on Aug 1, 2011 1:00 PM by 647939

    Enable / Disable Constraints.


      I am using Oracle 11g, i am still in the learning curve in pl sql where i got an strange situation regarding constraints disable / enable ,

      While moving datas(insert scripts from source) to the table(another copy of source) usually i will disable all the foregin constraints and enable it .

      While enableing the foregin constraints i got the error of ''Parent key not found".

      ALTER TABLE suppliers enable CONSTRAINT check_supplier_id;

      this can be over come by using the below query.

      'alter table '|| i.table_name|| ' MODIFY constraint '|| i.constraint_name|| ' ENABLE NOVALIDATE ';

      The question is the source contains the data properly but after moving the datas into destination enabling the constraints gives the error.(Need an solution for this )

      Parthiban Kumar
        • 1. Re: Enable / Disable Constraints.
          Welcome to the forums!

          I think the best course of action is to find the child records that don't have an associated parent. Once you do that you can then create the parent records (or correct your script) and then enable the constraint.

          Here is the basic skeleton for a query to retrieve the orphaned child records:
          SELECT child_primary_key_columns
          FROM   child_table
          WHERE  foreign_key_colums NOT IN ( SELECT primary_key_columns
                                             FROM   parent_table
          1 person found this helpful
          • 2. Re: Enable / Disable Constraints.
            To find, What is exactly going on you can take help from $ORACLE_HOME/rdbms/admin/utlexcpt.sql
            Also, search for "ALTER TABLE table_name ENABLE CONSTRAINT con_name EXCEPTIONS INTO EXCEPTIONS" in the Oracle documentation.
            • 3. Re: Enable / Disable Constraints.

              The solution to this problem is :
              - You can find the data of detail table, which are not there in master table ( because of which you are getting this error) ,
              - and then either insert those missing master records in master table or delete those extra detail table records,
              - then enable the foreign key constraint.

              For example say you have dept is your master table having deptno as primary key , and emp is detail table having deptno as foreign key referencing to dept table.
              Using Below query, you can find data which do not validate the foreign key on dept no.
              SELECT DISTINCT deptno
              FROM   emp
              WHERE  deptno NOT IN ( SELECT deptno
                                                 FROM   dept   )