3.7 Modifying Constraints
You can disable a constraint with the syntax:
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name ;
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name ;
If the data is bad, the ENABLE command will fail. The Oracle error message will
The NOVALIDATE option enables the constraint but for future transactions. The data present in the table does not have to comply with the constraint. This can have some serious consequences. For example, many
The NOVALIDATE option poses no threat if the nature of the task is to study and analyze historical data. You can leave the old, noncompliant data in the table, enable constraints, and proceed with loading some new data that you wish to conform to your business rules. But the NOVALIDATE option on an existing production system can break many applications.
When you DISABLE a primary key or unique constraint, the index is dropped. So, if the table is large, you may see some time delay when you enable the constraint. This is the time it would take to rebuild the index. The same holds if you DROP the constraint; the index is dropped.
You use a CASCADE option when you DISABLE or DROP a primary key of unique constraint that is referenced by a foreign key. This syntax is:
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name CASCADE; ALTER TABLE table_name DROP CONSTRAINT constraint_name CASCADE;
ORA-02272: this unique/primary key is referenced by some foreign key
3.8 Exception Handling
The PL/SQL built-in exception DUP_VAL_ON_INDEX is raised whenever a SQL statement
create or replace procedure INSERT_STUDENT ( v_student_name students.student_name%TYPE, v_college_major students.college_major%TYPE, v_status students.status%TYPE ) IS BEGIN INSERT INTO students (student_id, student_name, college_major, status) VALUES (students_pk_seq.nextval, v_student_name, v_college_major, v_status); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN dbms_output.put_line('We have a duplicate insert'); dbms_output.put_line('SQLERRM:'SQLERRM); dbms_output.put_line('SQLCODE:'SQLCODE); END insert_student;
Should the aforementioned procedure fail due to a duplicate primary key value, the output will be the following:
We have a duplicate insert SQLERRM:ORA-00001: unique constraint (SCOTT.PK_STUDENTS) violated SQLCODE:-1
The SQLCODE is an Oracle predefined function, that only has scope within an exception handler. The value of SQLCODE is not always the same as the Oracle ORA error number. In PL/SQL, your best approach to capture this specific constraint violation is to have an exception handler on the DUP_VAL_ON_INDEX exception. If your code ever enters that program scope, then you are sure you committed to either a primary key or unique constraint violation.
When developing applications with other languages, you need to look at the drivers. The Java code we see in Section 3.1.5, "Sequences in Code," uses the getErrorCode() method, which does not return a minus 1 but the five-digit number ”for a primary key constraint violation, a 1.
We have discussed error handling from an end user perspective; that is, capture primary constraint violations, no matter how rare they might be, and respond to the user with a meaningful message, still leaving the