Constraints and Indexes on Oracle Tables Using Data Dictionary Views

🔍 How to Inspect Constraints and Indexes on Oracle Tables Using Data Dictionary Views

Oracle provides powerful data dictionary views that let developers inspect the structural integrity of tables — including constraints (primary key, foreign key, check, unique) and indexes. Understanding how to query these views can help debug schema issues, optimize performance, and validate data modeling decisions.

In this post, we'll walk through how to test your tables at the metadata level using commonly used views like USER_CONSTRAINTS, USER_CONS_COLUMNS, and USER_INDEXES.

---

🎯 Why Inspect Tables at the Metadata Level?

There are several scenarios where you may want to inspect constraints and indexes:

Verifying that primary keys or foreign keys are defined properly

Checking which columns are indexed to improve query performance

Ensuring unique constraints are preventing duplicate data

Validating the status of constraints and indexes

---

🛠️ Key Data Dictionary Views

Here's a quick summary of Oracle views that help you achieve this:

View Name Purpose

USER_CONSTRAINTS Lists all constraints (PK, FK, UNIQUE, CHECK) for your own tables
USER_CONS_COLUMNS Shows which columns are involved in constraints
USER_INDEXES Lists all indexes (including their uniqueness and status)
USER_IND_COLUMNS Maps index names to column names

💡 Use DBA_ views if you're a DBA, or ALL_ views to see objects across schemas.

---

📌 Step-by-Step Testing Example

Assume you have a table called SBH_RES_JV_CODES. Let’s inspect everything about it:

---

1️⃣ View All Constraints on the Table

SELECT
constraint_name,
constraint_type,
table_name,
status,
index_name
FROM
user_constraints
WHERE
table_name = 'SBH_RES_JV_CODES';

Constraint Types:

P: Primary Key

U: Unique

R: Foreign Key (Referential)

C: Check

The INDEX_NAME column is useful — Oracle auto-creates indexes for P and U constraints.

---

2️⃣ View Columns Participating in Constraints

SELECT
constraint_name,
column_name,
position
FROM
user_cons_columns
WHERE
table_name = 'SBH_RES_JV_CODES';

This gives you a breakdown of which columns are part of each constraint.

---

3️⃣ View All Indexes on the Table

SELECT
index_name,
uniqueness,
table_name,
status
FROM
user_indexes
WHERE
table_name = 'SBH_RES_JV_CODES';

You can check:

Whether indexes are UNIQUE or NONUNIQUE

The STATUS (e.g., VALID)

---

4️⃣ Combine Constraints and Column Details

To get a holistic view of constraint type, columns, and associated index:

SELECT
uc.constraint_name,
uc.constraint_type,
ucc.column_name,
uc.index_name
FROM
user_constraints uc
JOIN
user_cons_columns ucc
ON uc.constraint_name = ucc.constraint_name
WHERE
uc.table_name = 'SBH_RES_JV_CODES';

---

🔁 Bonus: Index Columns

If you want to check which columns are indexed and in what order:

SELECT
index_name,
column_name,
column_position
FROM
user_ind_columns
WHERE
table_name = 'SBH_RES_JV_CODES';

---

✅ Final Thoughts

Testing tables at the metadata level using Oracle's dictionary views is essential for:

Maintaining data integrity

Understanding performance implications

Supporting schema debugging

Conducting audits and validations

By querying USER_CONSTRAINTS, USER_CONS_COLUMNS, and USER_INDEXES, developers and DBAs gain a deeper understanding of how their tables are structured and how Oracle enforces data rules.