About INSERT Statements and Constraints

When you run your installation script files, their INSERT statements insert the data from the source tables into the corresponding new tables. For each source table in your application, you must determine whether any constraints could be violated when their data is inserted in the new table. If so, you must first disable those constraints, then insert the data, and then try to re-enable the constraints. If a data item violates a constraint, you cannot re-enable that constraint until you correct the data item.

If you are simply inserting lookup data in correct order, constraints are not violated. Therefore, you do not need to disable them first.

If you are inserting data from an outside source (such as a file, spreadsheet, or older application), or from many tables that have much dependent data, disable the constraints before inserting the data.

Some possible ways to disable and re-enable the constraints are:

• Using SQL Developer, disable and re-enable the constraints one at a time.

• Edit the installation script file, adding SQL statements that disable and re-enable each constraint.

• Create a SQL script with SQL statements that disable and enable each constraint.

• Find the constraints in the Oracle Database data dictionary, and create a SQL script with the SQL statements to disable and enable each constraint.

  To find and enable the constraints used in the EVALUATIONS, PERFORMANCE_PARTS, and SCORES tables, enter these statements into a SQL Worksheet window:

  SELECT 'ALTER TABLE '|| TABLE_NAME || ' DISABLE CONSTRAINT '|| 
    CONSTRAINT_NAME ||';'
    FROM user_constraints
   WHERE table_name IN ('EVALUATIONS','PERFORMANCE_PARTS','SCORES');
   
  SELECT 'ALTER TABLE '|| TABLE_NAME || ' ENABLE CONSTRAINT '|| 
    CONSTRAINT_NAME ||';'
    FROM user_constraints
   WHERE table_name IN ('EVALUATIONS','PERFORMANCE_PARTS','SCORES');