5.5. USER_CONSTRAINTS
The USER_CONSTRAINTS view describes constraint types and detail attribute information on constraints. When diagnosing an Oracle constraint violation error message with a generic Oracle-generated constraint name, such as SYS_C012345, you look here to determine the type of constraint. If it is a FOREIGN KEY, then you might continue your search for the table name and column name of the parent. If it is a CHECK constraint, then you might look at the SEARCH_CONDITION to determine the rule of the CHECK constraint. When these steps are taken, you may likely look at USER_CONS_COLUMNS, using the constraint name, to determine the exact column name(s) on which the constraint is defined. The description of this view is:
SQL> DESC user_constraints Name Null? Type ------------------------------ -------- ---------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) GENERATED VARCHAR2(14) BAD VARCHAR2(3) RELY VARCHAR2(4) LAST_CHANGE DATE
The columns OWNER, CONSTRAINT_NAME, and TABLE_NAME have the same meaning as the view USER_CONS_COLUMNS. See the following:
| CONSTRAINT_TYPE | This single character is the constraint type. | |
| SEARCH_CONDITION | This stores the rule of a CHECK constraint; for example, “(AGE < 125).” For a NOT NULL constraint, the value is “CHECK OR NOT NULL.” | |
| R_OWNER | A foreign key constraint must reference a column that has a primary key or unique constraint. That column can be in the table of another schema. In this case, the column identifies that schema owner. If the table is not in another schema, this value equals the current schema. This value is only set for a foreign key constraint, or when CONSTRAINT_TYPE = R. | |
| R_CONSTRAINT_NAME | This value is only set for a foreign key constraint, or CONSTRAINT_TYPE = R. This column identifies the referenced primary key or unique constraint. A foreign key must always reference a column with a primary key or unique constraint. This value is not that column name, but rather the constraint name. | |
| DELETE_RULE | This is only set when the constraint is a foreign key. Values for this are: | |
| CASCADE: | The foreign key was created with the DELETE CASCADE rule. | |
| NO ACTION: | No DELETE CASCADE was used in the foreign key definition. | |
| STATUS | This equals ENABLED or DISABLED. | |
| DEFERRABLE | DEFERRABLE or NOT DEFERRABLE. | |
| DEFERRED | This is IMMEDIATE or DEFERRED. IMMEDIATE is the default. DEFERRED only applies to DEFERRABLE constraints. This is DEFERRED if the DEFERRABLE constraint is declared INITIALLY DEFERRED or if it is set to INITIALLY DEFERRED through an ALTER TABLE statement. | |
| VALIDATED | If a constraint is enabled with the NOVALIDATE option, this column will be set to NOT VALIDATED, otherwise it is VALIDATED. | |
| GENERATED | Set to GENERATED for Oracle-generated constraint names. Set to USER_NAME for user-generated names. | |
| BAD | A YES indicates that the constraint specifies a century in an ambiguous manner. This can be avoided with proper use of TO_CHAR functions. | |
| RELY | Indicates whether a constraint is enforced or unenforced. | |
| LAST_CHANGE | The last time the constraint was enabled. | |
| INDEX_OWNER | The owner of the index if the constraint is a primary key or unique constraint. | |
| INDEX_NAME | The name of the index. | |