Oracle query Constraints

Query constraint type

Select distinct constraint_type fromdba_constraints;

Type Code	Type description	Acts on Level
C	Check on a table	Column
O	Read only on a view	Object
P	Primary Key	Object
R	Referential aka foreign key	Column
U	Unique key	Column
V	Check option on a view	Object

 

 

 

 

 

 

Query various constraints

User_constraints all_constraints dba_constraints

 

Select * From user_constraints where constraint_type = 'R'

Select * fromall_constraints where constraint_type = 'P'

Select * From dba_constraintswhere constraint_type = 'C'

The following table lists the prefix names of a view:
· User _: the object created in current user mode
· All _: Objects Created in current user mode plus objects created by other users that can be accessed by the current user. The all view usually contains a "owner" column, reflecting the owner of the objects that can be accessed. The owner Column cannot be seen in the user_tables table because you are the owner of all tables in this view. There is a owner field in all_tables.
· DBA _: it provides information about the entire database. Including names and owners of all tables in the database-including basic tables in SYS Mode
These prefixes are helpful in limiting what we want to see, what we need to see, and what we should be allowed to see. View the table created in current mode and query user_tables;

 

Modify Constraints

Alter table table name: enable/disable/dropconstraint constraint name;