• We can use describe command to view logical structure of the table.
  • It displays table structure including column name, data type, size & not null constant.
  • It does not provide information about other constraint such as primary key, foreign key, unique key and so on.
  • Oracle maintains in built table known as USER_CONSTRAINTS.
  • It stores information about the constraints for different tables
  • If we ever wish to check information about different constraints defined by the user on different tables then  USER_CONSTRAINTS table can be helpful.
  • It contains following fields:
Column Name Description
Owner The owner of the constraint
Table_name Name of the table
Constraint_name Displays name of constraint
Constraint_type Displays constraint type:

1.      PK – Primary key        2. R – Foreign key     3. U – Unique key

C – Check key

 

R_owner Owner of the referenced table
Search_condition Search condition for check constraint
R_constraint_name Constraint name for the reference key
  • We can use SELECT statement to access information from the USER_CONSTRAINTS are follow.

Example:

SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE,
SEARCH_CONSTRAINT, TABLE_NAME FROM USER_CONSTRAINTS;