5.6 Data Dictionary Constraint Scripts

5.6.1 Constraints on a Table

The following query is a general script to query the constraints of a table. The use of the script accepts a table name as a command line argument.

 SQL> @my_const_are table_name 

Following the script text is the SQL*Plus output from the STUDENTS table.

 -- Filename: my_const_are.sql set verify off column column_name format a15 column table_name format a12 set pagesize 1000 SELECT SUBSTR(A.column_name,1,30) column_name, DECODE(B.constraint_type, 'P', 'PRIMARY KEY', 'U', 'UNIQUE KEY', 'C', 'CHECK OR NOT NULL', 'R', 'FOREIGN KEY') constraint_type, A.constraint_name FROM user_cons_columns A, user_constraints B WHERE A.table_name = UPPER('&1') AND A.table_name = B.table_name AND A.constraint_name = B.constraint_name AND a.owner = b.owner ORDER BY 2 DESC; clear columns 

The script file name is MY_CONST_ARE.SQL.

  SQL> @my_const_are students   COLUMN_NAME CONSTRAINT_TYPE CONSTRAINT_NAME   --------------- ----------------- -----------------------   STATE UNIQUE KEY UK_STUDENTS_LICENSE   LICENSE_NO UNIQUE KEY UK_STUDENTS_LICENSE   STUDENT_ID PRIMARY KEY PK_STUDENTS   STATE FOREIGN KEY FK_STUDENTS_STATE   STUDENT_ID CHECK OR NOT NULL SYS_C002073   STUDENT_NAME CHECK OR NOT NULL SYS_C002074   COLLEGE_MAJOR CHECK OR NOT NULL SYS_C002075   STATUS CHECK OR NOT NULL SYS_C002076  

All the constraint names that begin with SYS are NOT NULL constraints. As described in Chapter 3, an Oracle error message on these constraints includes the table name and column name.

5.6.2 Chasing a Constraint Name

You can investigate a particular constraint by joining the table and column name, from USER_CONS_COLUMNS view, with the definition of the constraint from the view USER_CONSTRAINTS. The following query is used to determine this information. If you were given a constraint with a SYS_C type name, this SQL would return the table name, column name, and constraint definition. Following the listing is the SQL*Plus session to investigate two constraints: the professor check constraint and a SYS named constraint.

 -- Filename: i_am.sql set verify off column column_name format a15 column table_name format a12 column constraint_name format a20 SELECT user_constraints.constraint_name, user_cons_columns.table_name, SUBSTR(user_cons_columns.column_name,1,30) column_name, DECODE(user_constraints.constraint_type, 'P', 'PRIMARY KEY', 'U', 'UNIQUE KEY', 'C', 'CHECK OR NOT NULL', 'R', 'FOREIGN KEY') constraint_type FROM user_cons_columns, user_constraints WHERE user_constraints.constraint_name = upper('&1') and user_constraints.owner=user_cons_columns.owner and user_constraints.constraint_name= user_cons_columns.constraint_name; clear columns 

The following shows the definition of a check constraint. The file name is I_AM.SQL.

  SQL>  @i_am ck_professors_tenure  CONSTRAINT_NAME TABLE_NAME COLUMN_NAME CONSTRAINT_TYPE   -------------------- ---------- ----------- -----------------   CK_PROFESSORS_TENURE PROFESSORS TENURE CHECK OR NOT NULL  

The next example tells us this is a CHECK or NOT NULL constraint on the STUDENTS STATUS column.

  SQL>  @i_am SYS_C002633  CONSTRAINT_NAME TABLE_NAME COLUMN_NAME CONSTRAINT_TYPE   ------------------ ----------- ----------- -----------------   SYS_C002633 STUDENTS STATUS CHECK OR NOT NULL  

5.6.3 CHECK Constraint Rule

A NOT NULL constraint is a type of CHECK constraint. Sometimes we need to make this determination. The NOT NULL constraint is a standard definition, but if the rule is a complicated CHECK enforcement, we need to investigate. The rule for a check constraint is in the SEARCH_CONDITION column of the USER_CONSTRAINTS view. The next query returns the rule for a check constraint.

 -- Filename: my_rule_is.sql set verify off set arraysize 1 set long 75 SELECT search_condition FROM user_constraints WHERE constraint_name = upper('&1'); 

The following returns the rule for the check constraint: CK_PROFESSORS_TENURE

  SQL>  @my_rule_is ck_professors_tenure  SEARCH_CONDITION   ------------------------------------------   tenure IN ('YES','NO')  

You can select just your check constraints by filtering on the GENERATED column. A constraint name generated by Oracle has this column set to "GENERATED NAME." Constraints you name have this column set to " USER NAME."

The following selects the table, constraint name, and check constraint rule.

 set pagesize 0 SELECT 'T='TABLE_NAME' ' 'C='CONSTRAINT_NAME, SEARCH_CONDITION FROM USER_CONSTRAINTS WHERE GENERATED='USER NAME' AND CONSTRAINT_TYPE='C'; 

The results from this are:

 T=PROFESSORS C=CK_PROFESSORS_SALARY salary < 30000 T=PROFESSORS C=CK_PROFESSORS_DEPARTMENT department IN ('MATH','HIST','ENGL','SCIE') T=PROFESSORS C=CK_PROFESSORS_TENURE tenure IN ('YES','NO') T=STUDENTS C=CK_STUDENTS_ST_LIC (state IS NULL AND license_no IS NULL) OR (state IS NOT NULL AND T=STUDENTS C=CK_STUDENTS_STATUS status IN ('Degree','Certificate') 

5.6.4 Querying Parent Tables

The following SQL does a self-join on USER_CONSTRANTS to determine parent information.

To illustrate , the COURSES table has a foreign key called:

 FK_COURSES_ST_ID 

This constraint will appear in the column CONSTRAINT_NAME. This same row will have the following column values:

R_TABLE_NAME	The table name of the parent.
R_CONSTRAINT_NAME	The name of the primary key or unique constraint that the foreign key references.

 -- Filename: my_parents_are.sql set verify off column foreign_key format a30 heading 'foreign key' column parent_table format a12 heading 'parenttable' column parent_key format a17 heading 'parent key' SELECT a.constraint_name SUBSTR(DECODE(a.delete_rule,'NO ACTION','(N)', 'CASCADE','(C)'),1,3) foreign_key, b.table_name parent_table, a.r_constraint_name parent_key FROM user_constraints a, user_constraints b WHERE a.table_name = upper('&1') AND a.r_constraint_name = b.constraint_name; clear columns  SQL>  @my_parents_are students_courses  parent   foreign key table parent key   ------------------------------ ---------------------------   FK_STUDENTS_COURSES_ST_ID(N) STUDENTS PK_STUDENTS   FK_STUDENTS_COURSES_COURSE(N) COURSES PK_COURSES   FK_STUDENTS_COURSES_PROF(N) PROFESSORS PK_PROFESSORS  

How would you determine the table column name that is referenced by a foreign key constraint? You know the name of the foreign key constraint. What is the parent column name?

• Query USER_CONSTRAINTS using the foreign key constraint name.

• Note the values of R_TABLE_NAME and R_CONSTRAINT_NAME.

• Query USER_CONS_COLUMNS using the R_TABLE_NAME and R_CONSTRAINT_NAME.

• Note the value(s) of COLUMN_NAME and POSITION.

5.6.5 Querying Child Tables

This script is similar to the parent script from earlier. It does a self-join on USER_CONSTRAINTS. To find a child table, look for a constraint name in the CONSTRAINT_NAME column. Then look for that same name elsewhere in the column R_CONSTRAINT_NAME ”at that row the value of CONSTRAINT_NAME is a foreign key.

 -- Filename: my_children_are.sql set verify off column parent_key format a15 column child_table format a16 column child_key format a28 SELECT a.r_constraint_name parent_key, a.table_name child_table, a.constraint_name SUBSTR(DECODE(a.delete_rule,'NO ACTION','(N)', 'CASCADE','(C)'),1,3) child_key FROM user_constraints A, user_constraints B WHERE B.table_name = upper('&1') AND a.r_constraint_name = b.constraint_name ORDER BY a.table_name; clear columns 

Sample query:

  SQL>  @my_children_are state_lookup  PARENT_KEY CHILD_TABLE CHILD_KEY   --------------- ---------------- ---------------------------   PK_STATE_LOOKUP STUDENTS FK_STUDENTS_STATE(N)   PK_STATE_LOOKUP STUDENT_VEHICLES FK_STUDENT_VEHICLES_STAT(N)  

5.6.6 Constraint Status

You can check the ENABLE/DISABLE status of a constraint. The following disables the primary key in the STUDENTS table, plus all foreign key constraints that reference that key.

 ALTER TABLE students DISABLE CONSTRAINT pk_students CASCADE; 

To see what constraints are disabled:

 SELECT table_name, constraint_name, status FROM user_constraints WHERE status='DISABLED' 

The result is:

 TABLE_NAME CONSTRAINT_NAME STATUS ---------------- ------------------------- -------- STUDENTS PK_STUDENTS DISABLED STUDENTS_COURSES FK_STUDENTS_COURSES_ST_ID DISABLED STUDENT_VEHICLES FK_STUDENT_VEHICLES_STUD DISABLED 

The primary key to STUDENTS can be enabled with the following:

 SQL> ALTER TABLE students ENABLE CONSTRAINT pk_students; Table altered . 

The foreign key constraints are still disabled. They have to be enabled individually.

 SELECT table_name, constraint_name, status FROM user_constraints WHERE status='DISABLED' 

The result is:

 TABLE_NAME CONSTRAINT_NAME STATUS ---------------- ------------------------- -------- STUDENTS_COURSES FK_STUDENTS_COURSES_ST_ID DISABLED STUDENT_VEHICLES FK_STUDENT_VEHICLES_STUD DISABLED 

These constraints can be enabled with:

 ALTER TABLE students_courses ENABLE CONSTRAINT fk_students_courses_st_id; ALTER TABLE student_vehicles ENABLE CONSTRAINT fk_student_vehicles_stud; 

5.6.7 Validated

We have a table called TEMP with duplicate values in the primary key column. This is causing some havoc in the system. The first task is to describe the table and check the status of the constraints. One possibility is that the constraint is DISABLED.

The example table description is:

 SQL> desc temp Name Null? Type -------------------------------- -------- --------------- TEMP_ID NUMBER(3) TEMP_DESC VARCHAR2(20) 

We check the status and last time change of the constraint.

 SELECT table_name, constraint_name, status, last_change FROM user_constraints WHERE table_name='TEMP' AND constraint_type='P'; 

The constraint is ENABLED. The LAST_CHANGE time can tell us if this constraint has recently been enabled and reenabled.

 TABLE_NAME CONSTRAINT_NAME STATUS LAST_CHANGE -------------- ------------------ -------- ------------ TEMP PK_TEMP ENABLED 07-aug 21:27 

The constraint is enabled but there are duplicates. This has to be a deferrable constraint. Data was loaded and the constraint was enabled with the NOVALIDATE option. We can verify this with the following:

 SELECT table_name, constraint_name, deferred, deferrable, validated FROM user_constraints WHERE deferrable='DEFERRABLE'; 

The result from this is the following:

 TABLE_NAME CONSTRAINT DEFERRED DEFERRABLE VALIDATED ---------- ---------- --------- -------------- ------------- TEMP PK_TEMP IMMEDIATE DEFERRABLE NOT VALIDATED 

The problem needs correction. There are duplicates and they must be removed. We can use an EXCEPTIONS table as described next:

 SQL> desc exceptions Name Null? Type -------------------------------- -------- ------------ ROW_ID ROWID OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) CONSTRAINT VARCHAR2(30) 

You can enable the constraint using the exceptions table. The offending rows are recorded in the EXCEPTIONS table.

You should delete any existing rows in the exceptions table first.

 SQL> ALTER TABLE temp ENABLE CONSTRAINT pk_temp 2 EXCEPTIONS INTO EXCEPTIONS; ORA-02437: cannot validate (SCOTT.PK_TEMP) - primary key violated 

The EXCEPTIONS table has each row that is a duplicate. First, let's query the problem table.

 SQL> SELECT * FROM temp; TEMP_ID TEMP_DESC ---------- ------------------ 1 Record One 1 Record Two 3 Record Three 4 Record Four 

For this small table, the duplicate rows are easily detected . A larger table would require a join of EXCEPTIONS and TEMP:

 SELECT temp_id,temp_desc FROM temp, exceptions WHERE temp.rowid=exceptions.row_id; 

The result from this query is:

 TEMP_ID TEMP_DESC ------- -------------------- 1 Record One 1 Record Two 

The table can be corrected with an update changing TEMP_ID to "2" for "Record Two." Once this is done all column values will be unique. The next step is to set the constraint to the state VALIDATE.

 UPDATE temp SET temp_id=2 WHERE temp_desc='Record Two'; ALTER TABLE temp ENABLE CONSTRAINT pk_temp; 

The constraint is enabled and validated. To rerun the aforementioned query:

 SELECT table_name, constraint_name, deferred, deferrable, validated FROM user_constraints WHERE deferrable='DEFERRABLE'; 

We see that the constraint is VALIDATED.

 TABLE_NAME CONSTRAINT DEFERRED DEFERRABLE VALIDATED ---------- ---------- --------- -------------- ------------- TEMP PK_TEMP IMMEDIATE DEFERRABLE VALIDATED