ORA-02291 Integrity Constraint Violated - Foreign Key Reference Issues

ORA-02291: Integrity Constraint Violated - Parent Key Not Found

Error Overview

Error Text: ORA-02291: integrity constraint ([schema].[constraint_name]) violated - parent key not found

This error occurs when attempting to insert or update a record with a foreign key value that doesn’t exist in the referenced parent table. It’s a referential integrity violation that protects data consistency by ensuring all foreign key relationships point to valid parent records.

Understanding Referential Integrity

Foreign Key Relationship Structure

Referential Integrity Model
├── Parent Table (Referenced) - Contains primary/unique key
│   └── Primary Key: customer_id
├── Child Table (Referencing) - Contains foreign key
│   └── Foreign Key: customer_id → customers.customer_id
└── Constraint Rules
    ├── INSERT: Child FK must exist in parent
    ├── UPDATE: New FK value must exist in parent
    └── DELETE: Parent records with children require cascade/restrict

Common Scenarios

• Missing parent records - Referenced data not yet created
• Data loading order - Child data loaded before parent data
• Deleted parent records - Referenced data removed from parent table
• Data type mismatches - FK and PK have different data types
• Case sensitivity issues - String values with different cases

Diagnostic Steps

1. Identify the Constraint and Tables Involved

-- Parse constraint name from error message and get details
SELECT
  c.owner,
  c.constraint_name,
  c.table_name as child_table,
  c.r_owner as parent_schema,
  rc.table_name as parent_table,
  c.constraint_type,
  c.status,
  c.validated
FROM dba_constraints c, dba_constraints rc
WHERE c.constraint_name = 'CONSTRAINT_NAME_FROM_ERROR'  -- Replace with actual constraint name
  AND c.r_constraint_name = rc.constraint_name(+)
  AND c.r_owner = rc.owner(+);

-- Get column mapping for the foreign key
SELECT
  cc.owner,
  cc.constraint_name,
  cc.table_name,
  cc.column_name as child_column,
  cc.position,
  rcc.table_name as parent_table,
  rcc.column_name as parent_column
FROM dba_cons_columns cc, dba_constraints c, dba_cons_columns rcc
WHERE cc.constraint_name = 'CONSTRAINT_NAME_FROM_ERROR'
  AND cc.owner = c.owner
  AND cc.constraint_name = c.constraint_name
  AND c.r_constraint_name = rcc.constraint_name
  AND c.r_owner = rcc.owner
  AND cc.position = rcc.position
ORDER BY cc.position;

-- Alternative: Find constraints by table name if constraint name is unclear
SELECT
  c.constraint_name,
  c.table_name as child_table,
  rc.table_name as parent_table,
  LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) as child_columns,
  LISTAGG(rcc.column_name, ', ') WITHIN GROUP (ORDER BY rcc.position) as parent_columns
FROM dba_constraints c, dba_constraints rc, dba_cons_columns cc, dba_cons_columns rcc
WHERE c.table_name = UPPER('child_table_name')  -- Replace with actual table
  AND c.constraint_type = 'R'  -- Foreign key
  AND c.r_constraint_name = rc.constraint_name
  AND c.r_owner = rc.owner
  AND c.owner = cc.owner
  AND c.constraint_name = cc.constraint_name
  AND rc.owner = rcc.owner
  AND rc.constraint_name = rcc.constraint_name
  AND cc.position = rcc.position
GROUP BY c.constraint_name, c.table_name, rc.table_name
ORDER BY c.constraint_name;

2. Find Missing Parent Records

-- Find specific missing parent record (replace with actual values)
SELECT 'Missing parent record' as issue
FROM dual
WHERE NOT EXISTS (
  SELECT 1 FROM parent_table
  WHERE parent_key_column = 'problematic_value'
);

-- Find all missing parent references for a foreign key
SELECT
  child.foreign_key_column,
  COUNT(*) as orphaned_count,
  MIN(child.rowid) as sample_rowid
FROM child_table child
WHERE NOT EXISTS (
  SELECT 1 FROM parent_table parent
  WHERE parent.primary_key_column = child.foreign_key_column
)
GROUP BY child.foreign_key_column
ORDER BY orphaned_count DESC;

-- Check for case sensitivity or data type issues
SELECT
  DISTINCT child.foreign_key_column as child_value,
  child.foreign_key_column as child_hex,
  (SELECT parent.primary_key_column
   FROM parent_table parent
   WHERE UPPER(TRIM(parent.primary_key_column)) = UPPER(TRIM(child.foreign_key_column))
   AND ROWNUM = 1) as parent_match,
  CASE
    WHEN EXISTS (SELECT 1 FROM parent_table WHERE primary_key_column = child.foreign_key_column) THEN 'EXACT_MATCH'
    WHEN EXISTS (SELECT 1 FROM parent_table WHERE UPPER(primary_key_column) = UPPER(child.foreign_key_column)) THEN 'CASE_DIFFERENT'
    WHEN EXISTS (SELECT 1 FROM parent_table WHERE TRIM(primary_key_column) = TRIM(child.foreign_key_column)) THEN 'WHITESPACE_DIFFERENT'
    WHEN EXISTS (SELECT 1 FROM parent_table WHERE UPPER(TRIM(primary_key_column)) = UPPER(TRIM(child.foreign_key_column))) THEN 'CASE_AND_WHITESPACE'
    ELSE 'NO_MATCH'
  END as match_type
FROM child_table child
WHERE NOT EXISTS (
  SELECT 1 FROM parent_table parent
  WHERE parent.primary_key_column = child.foreign_key_column
)
AND ROWNUM <= 10  -- Limit for analysis
ORDER BY child.foreign_key_column;

-- Check for NULL values (which are allowed in foreign keys)
SELECT
  COUNT(*) as total_child_records,
  COUNT(foreign_key_column) as non_null_fk,
  COUNT(*) - COUNT(foreign_key_column) as null_fk_count,
  ROUND((COUNT(foreign_key_column) / COUNT(*)) * 100, 2) as non_null_percentage
FROM child_table;

3. Analyze Data Relationships and Dependencies

-- Check parent table data availability
SELECT
  COUNT(*) as total_parent_records,
  MIN(primary_key_column) as min_key,
  MAX(primary_key_column) as max_key,
  COUNT(DISTINCT primary_key_column) as unique_keys
FROM parent_table;

-- Look for patterns in missing references
SELECT
  SUBSTR(child.foreign_key_column, 1, 3) as key_prefix,
  COUNT(*) as missing_count,
  MIN(child.foreign_key_column) as first_missing,
  MAX(child.foreign_key_column) as last_missing
FROM child_table child
WHERE NOT EXISTS (
  SELECT 1 FROM parent_table parent
  WHERE parent.primary_key_column = child.foreign_key_column
)
GROUP BY SUBSTR(child.foreign_key_column, 1, 3)
ORDER BY missing_count DESC;

-- Check for timing issues (child records created before parent)
SELECT
  TRUNC(child.created_date) as creation_date,
  COUNT(*) as orphaned_records,
  MIN(child.foreign_key_column) as sample_missing_key
FROM child_table child
WHERE NOT EXISTS (
  SELECT 1 FROM parent_table parent
  WHERE parent.primary_key_column = child.foreign_key_column
)
AND child.created_date IS NOT NULL
GROUP BY TRUNC(child.created_date)
ORDER BY creation_date DESC;

-- Check for data in wrong parent table (if multiple possible parents)
SELECT
  'alternate_parent_table' as possible_parent,
  COUNT(*) as matches_found
FROM alternate_parent_table alt, child_table child
WHERE alt.potential_key = child.foreign_key_column
  AND NOT EXISTS (
    SELECT 1 FROM parent_table parent
    WHERE parent.primary_key_column = child.foreign_key_column
  );

4. Examine Constraint Status and History

-- Check constraint status and validation
SELECT
  constraint_name,
  table_name,
  status,
  validated,
  rely,
  last_change,
  CASE
    WHEN status = 'ENABLED' AND validated = 'VALIDATED' THEN 'FULLY_ENFORCED'
    WHEN status = 'ENABLED' AND validated = 'NOT VALIDATED' THEN 'ENABLED_BUT_NOT_VALIDATED'
    WHEN status = 'DISABLED' THEN 'NOT_ENFORCED'
    ELSE 'UNKNOWN'
  END as enforcement_status
FROM dba_constraints
WHERE constraint_name = 'CONSTRAINT_NAME_FROM_ERROR'
   OR (table_name = UPPER('child_table') AND constraint_type = 'R');

-- Check for recent constraint changes
SELECT
  owner,
  object_name,
  object_type,
  last_ddl_time,
  status
FROM dba_objects
WHERE object_name LIKE '%CONSTRAINT_NAME%'
   OR (object_name = UPPER('child_table') AND object_type = 'TABLE')
ORDER BY last_ddl_time DESC;

-- Look for exceptions in constraint validation (if any)
SELECT
  owner,
  table_name,
  constraint_name,
  status,
  CASE status
    WHEN 'ENABLED' THEN 'Constraint is active and enforcing'
    WHEN 'DISABLED' THEN 'Constraint is not enforcing - check why'
    ELSE 'Unknown status'
  END as description
FROM dba_constraints
WHERE table_name = UPPER('child_table')
  AND constraint_type = 'R'
ORDER BY constraint_name;

Immediate Solutions

Solution 1: Insert Missing Parent Records

Create Required Parent Data

-- First, identify what parent records are needed
SELECT DISTINCT child.foreign_key_column
FROM child_table child
WHERE NOT EXISTS (
  SELECT 1 FROM parent_table parent
  WHERE parent.primary_key_column = child.foreign_key_column
)
ORDER BY child.foreign_key_column;

-- Insert minimal parent records to satisfy foreign key
INSERT INTO parent_table (primary_key_column, required_column, created_date)
SELECT DISTINCT
  child.foreign_key_column,
  'AUTO_CREATED_' || child.foreign_key_column as required_column,
  SYSDATE
FROM child_table child
WHERE NOT EXISTS (
  SELECT 1 FROM parent_table parent
  WHERE parent.primary_key_column = child.foreign_key_column
)
AND child.foreign_key_column IS NOT NULL;

-- For more complex parent records with business logic
DECLARE
  CURSOR missing_parents IS
    SELECT DISTINCT child.foreign_key_column as missing_key
    FROM child_table child
    WHERE NOT EXISTS (
      SELECT 1 FROM parent_table parent
      WHERE parent.primary_key_column = child.foreign_key_column
    )
    AND child.foreign_key_column IS NOT NULL;

BEGIN
  FOR parent_rec IN missing_parents LOOP
    -- Create parent record with appropriate defaults
    INSERT INTO parent_table (
      primary_key_column,
      name,
      status,
      created_date,
      created_by,
      description
    ) VALUES (
      parent_rec.missing_key,
      'Auto-created for key: ' || parent_rec.missing_key,
      'PLACEHOLDER',
      SYSDATE,
      'DATA_REPAIR_PROCESS',
      'Created to resolve referential integrity violation'
    );

    DBMS_OUTPUT.PUT_LINE('Created parent record: ' || parent_rec.missing_key);
  END LOOP;

  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Finished creating missing parent records');
END;
/

-- Verify all foreign key violations are resolved
SELECT COUNT(*) as remaining_violations
FROM child_table child
WHERE NOT EXISTS (
  SELECT 1 FROM parent_table parent
  WHERE parent.primary_key_column = child.foreign_key_column
)
AND child.foreign_key_column IS NOT NULL;

Solution 2: Fix or Remove Invalid Child Records

Clean Up Orphaned Child Records

-- Create backup of orphaned records before deletion
CREATE TABLE child_table_orphaned_backup AS
SELECT child.*, SYSDATE as backup_date
FROM child_table child
WHERE NOT EXISTS (
  SELECT 1 FROM parent_table parent
  WHERE parent.primary_key_column = child.foreign_key_column
)
AND child.foreign_key_column IS NOT NULL;

-- Option 1: Delete orphaned child records
DELETE FROM child_table
WHERE NOT EXISTS (
  SELECT 1 FROM parent_table parent
  WHERE parent.primary_key_column = child.foreign_key_column
)
AND foreign_key_column IS NOT NULL;

-- Option 2: Set foreign key to NULL (if column allows NULL)
UPDATE child_table
SET foreign_key_column = NULL,
    last_updated = SYSDATE,
    updated_by = 'DATA_REPAIR_PROCESS'
WHERE NOT EXISTS (
  SELECT 1 FROM parent_table parent
  WHERE parent.primary_key_column = child.foreign_key_column
)
AND foreign_key_column IS NOT NULL;

-- Option 3: Map to valid parent record (if business logic allows)
UPDATE child_table
SET foreign_key_column = 'DEFAULT_PARENT_KEY',
    last_updated = SYSDATE,
    updated_by = 'DATA_REPAIR_PROCESS'
WHERE NOT EXISTS (
  SELECT 1 FROM parent_table parent
  WHERE parent.primary_key_column = child.foreign_key_column
)
AND foreign_key_column IS NOT NULL
AND 'DEFAULT_PARENT_KEY' IN (SELECT primary_key_column FROM parent_table);

-- Option 4: Fix data type or format issues
UPDATE child_table
SET foreign_key_column = TRIM(UPPER(foreign_key_column))
WHERE foreign_key_column != TRIM(UPPER(foreign_key_column))
  AND EXISTS (
    SELECT 1 FROM parent_table
    WHERE primary_key_column = TRIM(UPPER(child_table.foreign_key_column))
  );

Solution 3: Temporarily Disable Constraint

Controlled Constraint Management

-- Check current constraint status
SELECT constraint_name, status, validated
FROM user_constraints
WHERE constraint_name = 'CONSTRAINT_NAME_FROM_ERROR';

-- Disable constraint temporarily (use with caution)
ALTER TABLE child_table DISABLE CONSTRAINT constraint_name_from_error;

-- Perform data operations that were failing
-- INSERT INTO child_table VALUES (...);

-- Re-enable constraint with validation
ALTER TABLE child_table ENABLE CONSTRAINT constraint_name_from_error;

-- Alternative: Enable without immediate validation (faster for large tables)
ALTER TABLE child_table ENABLE NOVALIDATE CONSTRAINT constraint_name_from_error;

-- Later validate the constraint when convenient
ALTER TABLE child_table MODIFY CONSTRAINT constraint_name_from_error VALIDATE;

-- Create procedure for safe constraint management
CREATE OR REPLACE PROCEDURE manage_constraint_safely(
  p_table_name VARCHAR2,
  p_constraint_name VARCHAR2,
  p_operation VARCHAR2  -- 'DISABLE', 'ENABLE', 'VALIDATE'
) AS
  v_current_status VARCHAR2(20);
  v_validated VARCHAR2(20);
BEGIN
  -- Check current status
  SELECT status, validated
  INTO v_current_status, v_validated
  FROM user_constraints
  WHERE constraint_name = UPPER(p_constraint_name);

  CASE UPPER(p_operation)
    WHEN 'DISABLE' THEN
      IF v_current_status = 'ENABLED' THEN
        EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' DISABLE CONSTRAINT ' || p_constraint_name;
        DBMS_OUTPUT.PUT_LINE('Constraint ' || p_constraint_name || ' disabled');
      ELSE
        DBMS_OUTPUT.PUT_LINE('Constraint ' || p_constraint_name || ' already disabled');
      END IF;

    WHEN 'ENABLE' THEN
      IF v_current_status = 'DISABLED' THEN
        EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' ENABLE CONSTRAINT ' || p_constraint_name;
        DBMS_OUTPUT.PUT_LINE('Constraint ' || p_constraint_name || ' enabled');
      ELSE
        DBMS_OUTPUT.PUT_LINE('Constraint ' || p_constraint_name || ' already enabled');
      END IF;

    WHEN 'VALIDATE' THEN
      IF v_current_status = 'ENABLED' AND v_validated = 'NOT VALIDATED' THEN
        EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' MODIFY CONSTRAINT ' || p_constraint_name || ' VALIDATE';
        DBMS_OUTPUT.PUT_LINE('Constraint ' || p_constraint_name || ' validated');
      ELSE
        DBMS_OUTPUT.PUT_LINE('Constraint ' || p_constraint_name || ' already validated or disabled');
      END IF;

    ELSE
      RAISE_APPLICATION_ERROR(-20001, 'Invalid operation: ' || p_operation);
  END CASE;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20002, 'Constraint not found: ' || p_constraint_name);
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20003, 'Error managing constraint: ' || SQLERRM);
END;
/

Long-Term Solutions

1. Implement Proper Data Loading Procedures

Ordered Data Loading Framework

-- Create data loading dependency table
CREATE TABLE data_load_dependencies (
  table_name VARCHAR2(30) PRIMARY KEY,
  load_order NUMBER NOT NULL,
  depends_on VARCHAR2(1000), -- Comma-separated list of prerequisite tables
  description VARCHAR2(200),
  validation_query VARCHAR2(1000)
);

-- Define loading order for tables with foreign key dependencies
INSERT INTO data_load_dependencies VALUES ('COUNTRIES', 1, NULL, 'Reference data - no dependencies', 'SELECT COUNT(*) FROM countries');
INSERT INTO data_load_dependencies VALUES ('REGIONS', 2, 'COUNTRIES', 'Regions depend on countries', 'SELECT COUNT(*) FROM regions');
INSERT INTO data_load_dependencies VALUES ('CUSTOMERS', 3, 'COUNTRIES,REGIONS', 'Customers depend on location data', 'SELECT COUNT(*) FROM customers');
INSERT INTO data_load_dependencies VALUES ('ORDERS', 4, 'CUSTOMERS', 'Orders depend on customers', 'SELECT COUNT(*) FROM orders');
INSERT INTO data_load_dependencies VALUES ('ORDER_ITEMS', 5, 'ORDERS,PRODUCTS', 'Order items depend on orders and products', 'SELECT COUNT(*) FROM order_items');

-- Data loading procedure with dependency checking
CREATE OR REPLACE PROCEDURE load_data_with_dependencies AS
  CURSOR load_order_cursor IS
    SELECT table_name, depends_on, validation_query
    FROM data_load_dependencies
    ORDER BY load_order;

  v_dependency_met BOOLEAN;
  v_dependency_list apex_t_varchar2;
  v_validation_count NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Starting data load with dependency checking...');

  FOR table_rec IN load_order_cursor LOOP
    v_dependency_met := TRUE;

    -- Check dependencies
    IF table_rec.depends_on IS NOT NULL THEN
      v_dependency_list := apex_string.split(table_rec.depends_on, ',');

      FOR i IN 1..v_dependency_list.COUNT LOOP
        -- Check if dependency table has data
        EXECUTE IMMEDIATE
          'SELECT COUNT(*) FROM ' || TRIM(v_dependency_list(i))
        INTO v_validation_count;

        IF v_validation_count = 0 THEN
          DBMS_OUTPUT.PUT_LINE('ERROR: Dependency not met - ' || TRIM(v_dependency_list(i)) || ' is empty');
          v_dependency_met := FALSE;
          EXIT;
        END IF;
      END LOOP;
    END IF;

    IF v_dependency_met THEN
      -- Proceed with loading (this would call specific loading procedures)
      DBMS_OUTPUT.PUT_LINE('Loading data for: ' || table_rec.table_name);

      -- Example: EXECUTE IMMEDIATE 'CALL load_' || LOWER(table_rec.table_name) || '_data()';

      -- Validate after loading
      IF table_rec.validation_query IS NOT NULL THEN
        EXECUTE IMMEDIATE table_rec.validation_query INTO v_validation_count;
        DBMS_OUTPUT.PUT_LINE('  ' || table_rec.table_name || ' loaded: ' || v_validation_count || ' records');
      END IF;
    ELSE
      RAISE_APPLICATION_ERROR(-20001, 'Cannot load ' || table_rec.table_name || ' - dependencies not met');
    END IF;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Data loading completed successfully');
END;
/

2. Implement Referential Integrity Monitoring

Comprehensive FK Violation Detection

-- Create foreign key monitoring system
CREATE TABLE fk_violations_log (
  violation_id NUMBER PRIMARY KEY,
  constraint_name VARCHAR2(30),
  child_table VARCHAR2(30),
  parent_table VARCHAR2(30),
  violation_count NUMBER,
  sample_orphaned_keys VARCHAR2(1000),
  detection_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  resolved CHAR(1) DEFAULT 'N',
  resolution_date TIMESTAMP,
  resolution_method VARCHAR2(100)
);

-- Create procedure to check all foreign key constraints
CREATE OR REPLACE PROCEDURE check_all_fk_constraints AS
  CURSOR fk_constraint_cursor IS
    SELECT
      c.constraint_name,
      c.table_name as child_table,
      rc.table_name as parent_table,
      LISTAGG(cc.column_name, ',') WITHIN GROUP (ORDER BY cc.position) as child_columns,
      LISTAGG(rcc.column_name, ',') WITHIN GROUP (ORDER BY rcc.position) as parent_columns
    FROM dba_constraints c, dba_constraints rc, dba_cons_columns cc, dba_cons_columns rcc
    WHERE c.constraint_type = 'R'
      AND c.status = 'ENABLED'
      AND c.r_constraint_name = rc.constraint_name
      AND c.r_owner = rc.owner
      AND c.owner = cc.owner
      AND c.constraint_name = cc.constraint_name
      AND rc.owner = rcc.owner
      AND rc.constraint_name = rcc.constraint_name
      AND cc.position = rcc.position
      AND c.owner = USER  -- Check only current schema
    GROUP BY c.constraint_name, c.table_name, rc.table_name;

  v_violation_sql VARCHAR2(4000);
  v_violation_count NUMBER;
  v_sample_keys VARCHAR2(1000);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Checking foreign key constraints...');

  FOR fk_rec IN fk_constraint_cursor LOOP
    -- Build dynamic SQL to check for violations
    v_violation_sql :=
      'SELECT COUNT(*) FROM ' || fk_rec.child_table || ' c ' ||
      'WHERE (' || fk_rec.child_columns || ') IS NOT NULL ' ||
      'AND NOT EXISTS (SELECT 1 FROM ' || fk_rec.parent_table || ' p ' ||
      'WHERE ';

    -- Add column comparisons (simplified for single column FKs)
    v_violation_sql := v_violation_sql || 'p.' || fk_rec.parent_columns || ' = c.' || fk_rec.child_columns || ')';

    -- Execute violation check
    BEGIN
      EXECUTE IMMEDIATE v_violation_sql INTO v_violation_count;

      IF v_violation_count > 0 THEN
        -- Get sample of violating keys
        EXECUTE IMMEDIATE
          'SELECT LISTAGG(' || fk_rec.child_columns || ', '', '') WITHIN GROUP (ORDER BY ROWNUM) ' ||
          'FROM (SELECT DISTINCT ' || fk_rec.child_columns || ' FROM ' || fk_rec.child_table || ' c ' ||
          'WHERE (' || fk_rec.child_columns || ') IS NOT NULL ' ||
          'AND NOT EXISTS (SELECT 1 FROM ' || fk_rec.parent_table || ' p ' ||
          'WHERE p.' || fk_rec.parent_columns || ' = c.' || fk_rec.child_columns || ') ' ||
          'AND ROWNUM <= 5)'
        INTO v_sample_keys;

        -- Log violation
        INSERT INTO fk_violations_log (
          violation_id, constraint_name, child_table, parent_table,
          violation_count, sample_orphaned_keys
        ) VALUES (
          fk_violations_seq.NEXTVAL, fk_rec.constraint_name, fk_rec.child_table,
          fk_rec.parent_table, v_violation_count, v_sample_keys
        );

        DBMS_OUTPUT.PUT_LINE('VIOLATION: ' || fk_rec.constraint_name || ' - ' || v_violation_count || ' orphaned records');
      ELSE
        DBMS_OUTPUT.PUT_LINE('OK: ' || fk_rec.constraint_name);
      END IF;

    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR checking ' || fk_rec.constraint_name || ': ' || SQLERRM);
    END;
  END LOOP;

  COMMIT;
END;
/

3. Application-Level Data Validation

Implement FK Validation in Application Code

// Java example with foreign key validation
@Service
@Transactional
public class OrderService {

    private final CustomerRepository customerRepository;
    private final ProductRepository productRepository;
    private final OrderRepository orderRepository;

    public Order createOrder(OrderCreateRequest request) throws ValidationException {
        // Validate foreign key references before creating order
        validateOrderReferences(request);

        Order order = new Order();
        order.setCustomerId(request.getCustomerId());
        order.setOrderDate(request.getOrderDate());

        // Add order items with validation
        for (OrderItemRequest itemRequest : request.getItems()) {
            validateOrderItemReferences(itemRequest);

            OrderItem item = new OrderItem();
            item.setProductId(itemRequest.getProductId());
            item.setQuantity(itemRequest.getQuantity());
            item.setOrder(order);

            order.addOrderItem(item);
        }

        try {
            return orderRepository.save(order);
        } catch (DataIntegrityViolationException e) {
            if (e.getCause() instanceof SQLException) {
                SQLException sqlEx = (SQLException) e.getCause();
                if (sqlEx.getErrorCode() == 2291) { // ORA-02291
                    throw new ForeignKeyViolationException(
                        "Referenced data not found. Please ensure all referenced records exist.", e);
                }
            }
            throw e;
        }
    }

    private void validateOrderReferences(OrderCreateRequest request) throws ValidationException {
        // Check customer exists
        if (!customerRepository.existsById(request.getCustomerId())) {
            throw new ValidationException("Customer not found: " + request.getCustomerId());
        }

        // Check customer is active
        Customer customer = customerRepository.findById(request.getCustomerId())
            .orElseThrow(() -> new ValidationException("Customer not found: " + request.getCustomerId()));

        if (!"ACTIVE".equals(customer.getStatus())) {
            throw new ValidationException("Customer is not active: " + request.getCustomerId());
        }
    }

    private void validateOrderItemReferences(OrderItemRequest itemRequest) throws ValidationException {
        // Check product exists
        if (!productRepository.existsById(itemRequest.getProductId())) {
            throw new ValidationException("Product not found: " + itemRequest.getProductId());
        }

        // Check product is available
        Product product = productRepository.findById(itemRequest.getProductId())
            .orElseThrow(() -> new ValidationException("Product not found: " + itemRequest.getProductId()));

        if (!product.isAvailable()) {
            throw new ValidationException("Product is not available: " + itemRequest.getProductId());
        }

        if (product.getStockQuantity() < itemRequest.getQuantity()) {
            throw new ValidationException("Insufficient stock for product: " + itemRequest.getProductId());
        }
    }

    // Batch validation for bulk operations
    public List<Order> createOrders(List<OrderCreateRequest> requests) {
        List<Order> createdOrders = new ArrayList<>();
        List<String> validationErrors = new ArrayList<>();

        for (OrderCreateRequest request : requests) {
            try {
                validateOrderReferences(request);

                for (OrderItemRequest itemRequest : request.getItems()) {
                    validateOrderItemReferences(itemRequest);
                }

                Order order = createOrder(request);
                createdOrders.add(order);

            } catch (ValidationException e) {
                validationErrors.add("Order for customer " + request.getCustomerId() + ": " + e.getMessage());
            }
        }

        if (!validationErrors.isEmpty()) {
            logger.warn("Validation errors in batch order creation: {}", validationErrors);
        }

        return createdOrders;
    }
}

4. Database Design Improvements

Implement Cascade Options and Better Constraints

-- Create tables with appropriate cascade options
CREATE TABLE customers (
  customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email VARCHAR2(255) NOT NULL UNIQUE,
  first_name VARCHAR2(100),
  last_name VARCHAR2(100),
  status VARCHAR2(20) DEFAULT 'ACTIVE',
  created_date TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE TABLE orders (
  order_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  customer_id NUMBER NOT NULL,
  order_date DATE DEFAULT SYSDATE,
  status VARCHAR2(20) DEFAULT 'PENDING',
  total_amount NUMBER(10,2),

  -- Foreign key with proper naming and cascade options
  CONSTRAINT fk_orders_customer_id
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE,  -- Or RESTRICT based on business rules

  -- Check constraints for data validation
  CONSTRAINT ck_orders_status
    CHECK (status IN ('PENDING', 'CONFIRMED', 'SHIPPED', 'DELIVERED', 'CANCELLED')),
  CONSTRAINT ck_orders_total_amount
    CHECK (total_amount >= 0)
);

-- Add indexes for foreign key columns (performance)
CREATE INDEX ix_orders_customer_id ON orders(customer_id);
CREATE INDEX ix_orders_status_date ON orders(status, order_date);

-- Create lookup/reference tables with proper constraints
CREATE TABLE order_statuses (
  status_code VARCHAR2(20) PRIMARY KEY,
  status_name VARCHAR2(100) NOT NULL,
  description VARCHAR2(500),
  is_active CHAR(1) DEFAULT 'Y',
  display_order NUMBER
);

-- Insert reference data
INSERT INTO order_statuses VALUES ('PENDING', 'Pending', 'Order received but not confirmed', 'Y', 1);
INSERT INTO order_statuses VALUES ('CONFIRMED', 'Confirmed', 'Order confirmed and being processed', 'Y', 2);
INSERT INTO order_statuses VALUES ('SHIPPED', 'Shipped', 'Order shipped to customer', 'Y', 3);
INSERT INTO order_statuses VALUES ('DELIVERED', 'Delivered', 'Order delivered to customer', 'Y', 4);
INSERT INTO order_statuses VALUES ('CANCELLED', 'Cancelled', 'Order cancelled', 'Y', 5);

-- Modify orders table to use reference table
ALTER TABLE orders ADD CONSTRAINT fk_orders_status
FOREIGN KEY (status) REFERENCES order_statuses(status_code);

-- Create procedure to validate all foreign key relationships
CREATE OR REPLACE PROCEDURE validate_fk_design AS
  CURSOR fk_cursor IS
    SELECT
      c.constraint_name,
      c.table_name,
      c.delete_rule,
      CASE c.delete_rule
        WHEN 'CASCADE' THEN 'Will delete child records when parent is deleted'
        WHEN 'RESTRICT' THEN 'Prevents parent deletion if children exist'
        WHEN 'SET NULL' THEN 'Sets FK to NULL when parent is deleted'
        WHEN 'NO ACTION' THEN 'Default - prevents parent deletion if children exist'
        ELSE 'Unknown delete rule'
      END as delete_rule_description
    FROM user_constraints c
    WHERE c.constraint_type = 'R'
    ORDER BY c.table_name, c.constraint_name;

BEGIN
  DBMS_OUTPUT.PUT_LINE('Foreign Key Constraint Analysis:');
  DBMS_OUTPUT.PUT_LINE('=====================================');

  FOR fk_rec IN fk_cursor LOOP
    DBMS_OUTPUT.PUT_LINE('Table: ' || fk_rec.table_name);
    DBMS_OUTPUT.PUT_LINE('  Constraint: ' || fk_rec.constraint_name);
    DBMS_OUTPUT.PUT_LINE('  Delete Rule: ' || fk_rec.delete_rule || ' - ' || fk_rec.delete_rule_description);
    DBMS_OUTPUT.PUT_LINE('');
  END LOOP;
END;
/

Related Errors

• ORA-00001 - Unique constraint violated
• ORA-02292 - Integrity constraint violated - child record found
• ORA-01400 - Cannot insert NULL into column
• ORA-01407 - Cannot update column to NULL

Quick Reference

Emergency Response Steps

1. ✓ Identify the constraint and tables involved
2. ✓ Find the specific missing parent record(s)
3. ✓ Determine if parent data should be created or child data removed
4. ✓ Create missing parent records or fix child references
5. ✓ Validate all foreign key relationships are satisfied
6. ✓ Implement validation to prevent future violations

Quick Commands

-- Find missing parent references
SELECT DISTINCT child.fk_column
FROM child_table child
WHERE NOT EXISTS (SELECT 1 FROM parent_table WHERE pk_column = child.fk_column);

-- Create missing parent records
INSERT INTO parent_table (pk_column, name)
SELECT DISTINCT fk_column, 'Auto-created' FROM child_table
WHERE fk_column NOT IN (SELECT pk_column FROM parent_table);

-- Remove orphaned child records
DELETE FROM child_table
WHERE fk_column NOT IN (SELECT pk_column FROM parent_table);

-- Check constraint status
SELECT constraint_name, status, validated FROM user_constraints
WHERE constraint_type = 'R' AND table_name = 'CHILD_TABLE';

-- Disable/enable constraint
ALTER TABLE child_table DISABLE CONSTRAINT constraint_name;
ALTER TABLE child_table ENABLE CONSTRAINT constraint_name;

Prevention Guidelines

• Validate references before INSERT/UPDATE - Check parent records exist
• Load data in proper order - Parents before children
• Use cascade options appropriately - ON DELETE CASCADE/RESTRICT
• Implement application validation - Validate FKs in business logic
• Monitor referential integrity - Regular FK violation checks
• Design with reference tables - Use lookup tables for valid values