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?
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.
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.
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.
Constraint names are stored in ALL_CONSTRAINTS table. The column names on which constraints are defined can be found in ALL_CONS_COLUMNS.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With