Java bro

Java tutorial

Sql queries

Get constraints of a table

In this article we wre going to write a query to find contraint names existed on a table .

Query :

SELECT AC.CONSTRAINT_NAME , AC.CONSTRAINT_TYPE , AC.TABLE_NAME
FROM ALL_CONSTRAINTS AC
WHERE TABLE_NAME = ‘EMPLOYEE’;

Output :

CONSTRAINT_NAMECONSTRAINT_TYPETABLE_NAME
SYS_C0071731CEMPLOYEE
FK5C5779A82D9BC06EREMPLOYEE
SYS_C0071876PEMPLOYEE


1) Constraint_type C stands for check constraint on a table
2) Constraint_type R stands for referential integrity (Foriegn key) constraint on a table
3) Constraint_type P stands for primary key constraint on a table

If you want to get column names on which constraints applied , we have to use below query

SELECT ACONS.TABLE_NAME, ACONS.COLUMN_NAME, ACONS.CONSTRAINT_NAME
FROM ALL_CONS_COLUMNS ACONS
WHERE ACONS.TABLE_NAME = ‘EMPLOYEE’;

Output :

TABLE_NAMECOLUMN_NAMECONSTRAINT_NAME
EMPLOYEECOM_IDFK5C5779A82D9BC06E
EMPLOYEEEMP_IDSYS_C0071876
EMPLOYEEEMP_IDSYS_C0071731

Comments are Closed

Theme by Anders Norén