There are two data dictionary views that provide you detailed information about constraints. They are USER_CONSTRAINTS and USER_CONS_COLUMNS. These dictionary views are highly helpful to view the referntial integrity constraints information.

You can add a constraint to a table at the time of its creation or the constraint can be applied on a table by using ALTER TABLE statement.A table can have many constraints. For every constraint in a table there is a single row in USER_CONSTRAINTS table that describes that Particular constraint, including the table name to which that constraint is applied.

There are Five types of Constraints

1. NOT NULL CONSTRAINT
2. CHECK CONSTRAINT
3. Primay Key
4. Unique key Constraint
5. Foreign Key Constraint

If you know the constraint name and you want to view the constraint type , query the data dictionary view USER_CONSTRAINTS. uSER_CONSTRAINTS dictionary view only provides the constraint definition information. It does not provide with Column name(s) on which that constraint is defined.

The User_cons_columns view shows the columns in a constraint. if a foriegn key is a concatenated key of two fields, there will be two rows for that constraint in this view.

SQL> create table emp(emp_id number(10) primary key,
emp_name varchar2(50),
city varchar2(50));

In the above DDL statement i have created a table “emp” with emp_id as primary key. To view the constraint information for this table

SQL> select constraint_name,constraint_type,table_name
from user_constraints
where table_name=’EMP’;

CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME
—————————— ————– ——————————
SYS_C004505 P EMP

From the above query you can see that a row exists in user_constraints table showing the constraint_name as ‘SYS_C004505′. Since i have not given any constraint_name, Oracle itself has generated a constraint name. Usually oracle generated constraint names start with “SYS”. Constraint_type column indicates the type of constraint.

if the constraint_type is

P - denotes primary key constraint
C - denotes Not NULL OR check constraint
R - denotes Foreign key Constraint
U - denotes UNique Key constraint.

The above query does show the constraint definition and its type but does not show the information of the fields on which constraint is valid upon.

To find this take the constraint_name from the above query and execute another query as

SQL> SELECT OWNER,
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
POSITION
FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME=’SYS_C004505′ ;

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
——– —————- ———- ———– ——–
STRI SYS_C004505 EMP EMP_ID 1

Let us view the above data dictionary view with some other example. Below is an ER diagram representing three entities - supplier, Parts, Invoice. Supplier and parts are master entities where as Invoice is an relational entity explaining about which supplier had supplied which parts with quantity information.

And the Sql for the above diagram is

create table supplier(
supplier_id number(10) primary key,
supplier_name varchar2(50),
supplier_addr varchar2(200)
);

create table parts(
parts_id number(10) primary key,
part_name varchar2(100),
part_desc varchar2(200)
);

create table invoice(
invoice_id number(10),
invoice_date date,
supplier_id number(10) references supplier,
parts_id number(10) references parts,
primary key(invoice_id,invoice_date)
);

querying the user_constraints will result

SQL> select constraint_name,constraint_type,table_name
2 from user_constraints
3 where table_name=’SUPPLIER’;

CONSTRAINT_NAME C TABLE_NAME
—————————— - ——————————
SYS_C004506 P SUPPLIER

SQL> select constraint_name,constraint_type,table_name
2 from user_constraints
3 where table_name=’PARTS’;

CONSTRAINT_NAME C TABLE_NAME
—————————— - ——————————
SYS_C004507 P PARTS

SQL> select constraint_name,constraint_type,table_name
2 from user_constraints
3 where table_name=’INVOICE’;

CONSTRAINT_NAME C TABLE_NAME
—————————— - ——————————
SYS_C004508 P INVOICE
SYS_C004509 R INVOICE
SYS_C004510 R INVOICE

So user_constraints store one record for every constraint on a table.Frome the above query you can also see that constraint_type is ‘R’ for constraints SYS_C004509 and SYS_C004510 which indicates these are referential integrity constraints(foreign key).

SQL> SELECT
TABLE_NAME,
COLUMN_NAME,
POSITION
FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME=’SYS_C004506′;

TABLE_NAME COLUMN_NAME POSITION
———- ———– ——–
SUPPLIER SUPPLIER_ID 1

SQL> SELECT
TABLE_NAME,
COLUMN_NAME,
POSITION
FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME=’SYS_C004507′;

TABLE_NAME COLUMN_NAME POSITION
———- ———– ——–
PARTS PARTS_ID 1

Querying the invoice table for primary key constraint

SQL> SELECT
TABLE_NAME,
COLUMN_NAME,
POSITION
FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME=’SYS_C004508′;

TABLE_NAME COLUMN_NAME POSITION
———- ———– ——–
INVOICE INVOICE_ID 1
INVOICE INVOICE_DATE 2

From the above data you can see that primary key is a composite key which is made up of two columns resulting in two rows for that constraint depicting their positions.

SQL> SELECT
TABLE_NAME,
COLUMN_NAME,
POSITION
FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME=’SYS_C004509′;

TABLE_NAME COLUMN_NAME POSITION
———- ———– ——–
INVOICE SUPPLIER_ID 1

SQL> SELECT
TABLE_NAME,
COLUMN_NAME,
POSITION
FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME=’SYS_C004510′;

TABLE_NAME COLUMN_NAME POSITION
———- ———– ——–
INVOICE PARTS_ID 1

Then the next question could be how to find out the foreign key’s parent table and the fields which the foreign key is referencing.

There is one more field in the user_constraints dictionary view called r_constraint_name. R_constraint_name would have value only if the constraint_type value is ‘R’.

SQL> select constraint_name,constraint_type,table_name,R_CONSTRAINT_NAME
from user_constraints
where table_name=’INVOICE’;

CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME
————— - ——— —————-
SYS_C004508 P INVOICE
SYS_C004509 R INVOICE SYS_C004506
SYS_C004510 R INVOICE SYS_C004507

For the constraint_type ‘P’ you will find a null value in R_CONSTRAINT_NAME field. R_constraint_name value of ‘SYS_C004506′ points to supplier table supplier_id primary key. R_constraint_name value of ‘SYS_C004507′ points to parts table parts_id primary key.