In Oracle databases, constraints are vital for maintaining data integrity. They enforce rules on your data, such as primary keys (PK), unique keys (UK), foreign keys (FK), and check constraints. Whether you’re auditing existing constraints, recreating them after schema changes, or validating them, having a set of reliable SQL queries at your disposal can save hours of work.

This might be caused during impdp due to data from different character sets or broken relationships.

In this blog post, we’ll explore a collection of useful Oracle SQL queries tailored for constraint management. I’ve adapted these queries to focus on the standard HR schema (a common sample schema in Oracle) and the EMP table (representing a simple employee table). If you’re working with the HR schema, these can be run directly—assuming you have the necessary privileges (e.g., SELECT on ALL_CONSTRAINTS and ALL_CONS_COLUMNS).

We’ll break them down by purpose, explain what each does, and provide the ready-to-use SQL. These are great for DBAs, developers, or anyone migrating schemas or troubleshooting integrity issues.

1. Listing Basic Constraints for a Specific Table

This query retrieves key details about primary, unique, and foreign key constraints on a table, including referenced owners and constraints. It’s perfect for quick audits.SELECT constraint_name, table_name, constraint_type, r_owner AS referenced_owner, r_constraint_name AS referenced_pk FROM all_constraints WHERE owner = 'HR' AND TABLE_NAME='EMP' AND constraint_type in ('P', 'R','U');

How it works:

• Filters by owner (‘HR’) and table (‘EMP’).
• Constraint types: ‘P’ for Primary Key, ‘R’ for Referential (Foreign Key), ‘U’ for Unique.
• Use this to see what constraints exist and what they reference.

2. Detailed Constraint Columns and Status

This joins constraints with their columns, showing positions and status. Ideal for understanding the structure of constraints.SELECT ac.owner, ac.table_name, ac.constraint_name, ac.constraint_type, acc.column_name, acc.position, ac.status FROM all_constraints ac JOIN all_cons_columns acc ON ac.owner = acc.owner AND ac.constraint_name = acc.constraint_name WHERE ac.owner = 'HR' AND ac.TABLE_NAME='EMP' ORDER BY ac.table_name, ac.constraint_name, acc.position;

How it works:

• Joins ALL_CONSTRAINTS with ALL_CONS_COLUMNS for column-level details.
• Ordered for readability.
• Check the ‘status’ column to see if constraints are enabled or disabled.

3. Generating SQL to Recreate Constraints (Comprehensive Version)

This powerful query generates ALTER TABLE statements to recreate PK, UK, and FK constraints. It includes references for FKs and sorts them logically (PK first, then UK, then FK) to avoid dependency issues during recreation.SELECT CASE WHEN ac.constraint_type = 'R' THEN 'ALTER TABLE ' || ac.owner || '.' || ac.table_name || ' ADD CONSTRAINT ' || ac.constraint_name || ' FOREIGN KEY (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ')' || ' REFERENCES ' || ref.owner || '.' || ref.table_name || ' (' || (SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY position) FROM all_cons_columns WHERE owner = ref.owner AND constraint_name = ref.constraint_name) || ');' WHEN ac.constraint_type = 'P' THEN 'ALTER TABLE ' || ac.owner || '.' || ac.table_name || ' ADD CONSTRAINT ' || ac.constraint_name || ' PRIMARY KEY (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ');' WHEN ac.constraint_type = 'U' THEN 'ALTER TABLE ' || ac.owner || '.' || ac.table_name || ' ADD CONSTRAINT ' || ac.constraint_name || ' UNIQUE (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ');' END AS RECREATE_CONSTRAINTS_SQL FROM all_constraints ac JOIN all_cons_columns acc ON ac.owner = acc.owner AND ac.constraint_name = acc.constraint_name LEFT JOIN all_constraints ref ON ac.r_owner = ref.owner AND ac.r_constraint_name = ref.constraint_name WHERE ac.owner IN ('HR') AND ac.constraint_type IN ('P', 'R', 'U') GROUP BY ac.owner, ac.table_name, ac.constraint_name, ac.constraint_type, ref.owner, ref.table_name, ref.constraint_name ORDER BY DECODE(ac.constraint_type, 'P', 1, 'U', 2, 'R', 3), ac.owner, ac.table_name;

How it works:

• Uses LISTAGG to concatenate columns.
• Handles FK references with a subquery.
• Output is executable SQL—copy and paste to rebuild constraints.

4. Simplified Recreation for Primary Keys and More

A variant focused on generating PK, UK, and FK recreation SQL, without some extras.SELECT 'ALTER TABLE ' || ac.owner || '.' || ac.table_name || ' ADD CONSTRAINT ' || ac.constraint_name || ' PRIMARY KEY (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ');' AS RECREATE_CONSTRAINTS_SQL FROM all_constraints ac JOIN all_cons_columns acc ON ac.owner = acc.owner AND ac.constraint_name = acc.constraint_name WHERE ac.owner in( 'HR') AND ac.constraint_type in ('P', 'R','U') GROUP BY ac.owner, ac.table_name, ac.constraint_name;

How it works:

• Similar to query 3 but streamlined for PK-focused output.
• Adjust if you need full FK details.

5. Recreation with NOVALIDATE for Foreign Keys

This version adds ‘ENABLE NOVALIDATE’ for FKs, useful when recreating without immediate validation (e.g., for large datasets).SELECT CASE WHEN ac.constraint_type = 'R' THEN 'ALTER TABLE ' || ac.owner || '.' || ac.table_name || ' ADD CONSTRAINT ' || ac.constraint_name || ' FOREIGN KEY (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ')' || ' REFERENCES ' || ref.owner || '.' || ref.table_name || ' (' || (SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY position) FROM all_cons_columns WHERE owner = ref.owner AND constraint_name = ref.constraint_name) || ') ENABLE NOVALIDATE;' WHEN ac.constraint_type = 'P' THEN 'ALTER TABLE ' || ac.owner || '.' || ac.table_name || ' ADD CONSTRAINT ' || ac.constraint_name || ' PRIMARY KEY (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ');' WHEN ac.constraint_type = 'U' THEN 'ALTER TABLE ' || ac.owner || '.' || ac.table_name || ' ADD CONSTRAINT ' || ac.constraint_name || ' UNIQUE (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ');' END AS RECREATE_CONSTRAINTS_SQL FROM all_constraints ac JOIN all_cons_columns acc ON ac.owner = acc.owner AND ac.constraint_name = acc.constraint_name LEFT JOIN all_constraints ref ON ac.r_owner = ref.owner AND ac.r_constraint_name = ref.constraint_name WHERE ac.owner IN ('HR') AND ac.constraint_type IN ('P', 'R', 'U') GROUP BY ac.owner, ac.table_name, ac.constraint_name, ac.constraint_type, ref.owner, ref.table_name, ref.constraint_name ORDER BY DECODE(ac.constraint_type, 'P', 1, 'U', 2, 'R', 3), ac.owner, ac.table_name;

How it works:

• NOVALIDATE defers validation, speeding up application.
• Great for production migrations.

6. Recent Constraint Changes

Track constraints altered in the last 3 days—handy for monitoring recent schema updates.SELECT owner, table_name, constraint_name, constraint_type, status, validated, last_change AS last_altered_date, r_owner AS ref_owner, r_constraint_name AS ref_constraint FROM all_constraints WHERE constraint_type IN ('P', 'R', 'U') AND last_change >= SYSDATE - 3 ORDER BY last_change DESC, owner, table_name;

How it works:

• No owner filter here, but you can add WHERE owner = ‘HR’ if needed.
• Sorts by most recent changes.

7. Enabling Validation for NOVALIDATE Constraints

Generates commands to validate enabled but unvalidated constraints.SELECT 'ALTER TABLE ' || owner || '.' || table_name || ' ENABLE VALIDATE CONSTRAINT ' || constraint_name || ';' AS cmd FROM all_constraints WHERE status = 'ENABLED' AND validated = 'NOT VALIDATED' and owner IN ('HR');

How it works:

• Targets constraints that are enabled but not validated.
• Run the output to enforce validation.

8. Recreating Check Constraints

Check constraints enforce custom rules (e.g., salary > 0). This generates recreation SQL for them.SELECT 'ALTER TABLE ' || owner || '.' || table_name || ' ADD CONSTRAINT ' || constraint_name || ' CHECK (' || search_condition_vc || ');' AS recreate_check_sql FROM all_constraints WHERE owner IN ('HR') AND constraint_type = 'C' AND search_condition_vc IS NOT NULL;

How it works:

• Filters for ‘C’ type (Check).
• Includes the original condition.

9. Single-Schema Focused Recreation

A compact version for one schema, similar to query 3.SELECT CASE WHEN ac.constraint_type = 'R' THEN 'ALTER TABLE ' || ac.owner || '.' || ac.table_name || ' ADD CONSTRAINT ' || ac.constraint_name || ' FOREIGN KEY (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ')' || ' REFERENCES ' || ref.owner || '.' || ref.table_name || ' (' || (SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY position) FROM all_cons_columns WHERE owner = ref.owner AND constraint_name = ref.constraint_name) || ');' WHEN ac.constraint_type = 'P' THEN 'ALTER TABLE ' || ac.owner || '.' || ac.table_name || ' ADD CONSTRAINT ' || ac.constraint_name || ' PRIMARY KEY (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ');' WHEN ac.constraint_type = 'U' THEN 'ALTER TABLE ' || ac.owner || '.' || ac.table_name || ' ADD CONSTRAINT ' || ac.constraint_name || ' UNIQUE (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ');' END AS RECREATE_CONSTRAINTS_SQL FROM all_constraints ac JOIN all_cons_columns acc ON ac.owner = acc.owner AND ac.constraint_name = acc.constraint_name LEFT JOIN all_constraints ref ON ac.r_owner = ref.owner AND ac.r_constraint_name = ref.constraint_name WHERE ac.owner IN ('HR') AND ac.constraint_type IN ('P', 'R', 'U') GROUP BY ac.owner, ac.table_name, ac.constraint_name, ac.constraint_type, ref.owner, ref.table_name, ref.constraint_name ORDER BY DECODE(ac.constraint_type, 'P', 1, 'U', 2, 'R', 3), ac.owner, ac.table_name;

How it works:

• Tailored for ‘HR’—expand the IN clause for multi-schema use.

Wrapping Up

These queries are lifesavers for constraint management in Oracle. Start with the listing ones for audits, then use the recreation generators for schema exports or recoveries. Remember, run them with appropriate privileges, and test in a dev environment first. If your EMP table has specific constraints (e.g., EMP_ID as PK), these will highlight them beautifully.

If you have variations or need queries for indexes/triggers, drop a comment! Happy querying! 🚀