sql | Oracle‎ > ‎ora | PL/SQL‎ > ‎

ora | Select Contraints

SELECT 
     cols.table_name
    ,cols.column_name
    ,cols.position
    ,cons.status
    ,cons.owner
    ,cons.constraint_type
FROM
     all_constraints cons
    ,all_cons_columns cols
WHERE
    cols.table_name like 'TABLENAME'
    --AND cons.constraint_type = 'P' 
    AND cons.constraint_name = cols.constraint_name
    AND cons.owner = cols.owner
ORDER BY
     cols.table_name
    ,cols.position;
 
==================================================================================
 
Data Dictionary Objects Related To Constraints 
con$  ind$  icol$ 
ccol$ 
DBA ALL USER
dba_cons_columns all_cons_columns user_cons_columns
dba_constraints all_constraints user_constraints
dba_indexes all_indexes user_indexes
dba_ind_partitions all_ind_partitions user_ind_partitions
dba_ind_subpartitions all_ind_subpartitions user_ind_subpartitions
Constraint Types & Codes
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
 
Comments