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 'parent|table'
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