Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to see contents of Check Constraint on Oracle

I did not create the database I'm working with, but I would like to see the details of a check constraint.

I know a check constraint on a column is enforcing a set of specific values, and I'd like to know what those values are. For example, if a check constraint is enforcing the character 'Y' and 'N', I want to be able to query the database and see that the accepted values are 'Y' and 'N.'

Is this possible to do through a query?

like image 999
Chris Avatar asked Apr 21 '10 20:04

Chris


People also ask

How do I view constraints in Oracle SQL?

In Oracle, use the view user_constraints to display the names of the constraints in the database. The column constraint_name contains the name of the constraint, constraint_type indicates the type of constraint, and table_name contains the name of the table to which the constraint belongs.

How do you view constraints in a table?

Use the view table_constraints in the information_schema schema. The column table_name gives you the name of the table in which the constraint is defined, and the column constraint_name contains the name of the constraint.

How do I view constraints on a table in PL SQL Developer?

First method is with table Constraints tab (select table and select Constraints tab). Tab lists table constraints - primary, unique and foreign keys and check constraints - all in one grid. Foreign keys are the ones with 'Foreign_Key' value in CONSTRAINT_TYPE column.

Where can I find constraints in Oracle?

Constraint names are stored in ALL_CONSTRAINTS table. The column names on which constraints are defined can be found in ALL_CONS_COLUMNS.


1 Answers

select constraint_name,search_condition 
from all_constraints
where table_name='NAME_OF_YOUR_TABLE'
and constraint_type='C';

Will list the check and the constraint name of all check constraints on a specific table.

like image 144
DBA Avatar answered Oct 02 '22 19:10

DBA