DEV Community: umzzil nng

PostgreSQL 22026 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 16 Aug 2026 10:03:03 +0000

PostgreSQL Error 22026: string data length mismatch

PostgreSQL error code 22026 (string data length mismatch) occurs when the actual length of string data does not match the expected or required length defined for a column or data type. This error belongs to SQL standard class 22 (Data Exception) and is most commonly encountered with fixed-length types like CHAR(n) and BIT(n), or when exchanging data through binary protocols, COPY commands, or Foreign Data Wrappers (FDW). Understanding the root cause is essential, as this error directly impacts data integrity in production systems.

Top 3 Causes

1. Binary COPY with mismatched column lengths

When using COPY ... WITH (FORMAT BINARY), PostgreSQL encodes the string length in the binary stream. If the source data was exported from a CHAR(10) column but the target table defines the column as CHAR(8), a length mismatch error is thrown immediately.

-- This will fail if the binary file was created from a CHAR(10) column
COPY your_table (id, fixed_col)
FROM '/data/export.bin'
WITH (FORMAT BINARY);

-- Quick fix: switch to TEXT/CSV format
COPY your_table (id, fixed_col)
FROM '/data/export.csv'
WITH (FORMAT CSV, HEADER true);

-- Or use a staging table with TEXT type first
CREATE TEMP TABLE staging (id INT, fixed_col TEXT);
COPY staging FROM '/data/export.csv' WITH (FORMAT CSV, HEADER true);

INSERT INTO your_table (id, fixed_col)
SELECT id, fixed_col::CHAR(8)
FROM staging
WHERE char_length(trim(fixed_col)) <= 8;

2. Inserting wrong-length values into BIT(n) columns

Unlike VARCHAR, BIT(n) requires exactly n bits. Inserting a bit string of any other length triggers error 22026. This is a common pitfall when working with bitmask flags or binary encoded data from external systems.

-- This will raise ERROR 22026
-- INSERT INTO bit_flags (flags) VALUES (B'101');  -- only 3 bits for BIT(8)

-- Fix 1: pad the bit string to the correct length
INSERT INTO bit_flags (flags)
VALUES (lpad('101', 8, '0')::BIT(8));

-- Fix 2: use BIT VARYING(n) for flexible-length bit strings
ALTER TABLE bit_flags
  ALTER COLUMN flags TYPE BIT VARYING(8);

-- Now shorter bit strings are accepted
INSERT INTO bit_flags (flags) VALUES (B'101');

3. Type mapping errors in Foreign Data Wrappers (FDW)

When using FDWs like postgres_fdw or oracle_fdw, if the remote column is defined as CHAR(20) but the local foreign table maps it to CHAR(10), fetching or pushing data will raise 22026. The fix is to relax the local column type to TEXT and handle length enforcement locally.

-- Relax the foreign table column type to TEXT
ALTER FOREIGN TABLE remote_customers
  ALTER COLUMN customer_code TYPE TEXT;

-- Apply length validation when inserting into local table
INSERT INTO local_customers (id, customer_code)
SELECT id,
       LEFT(customer_code, 10)::CHAR(10)
FROM remote_customers
WHERE customer_code IS NOT NULL;

-- Verify column definitions on both sides
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'remote_customers';

Quick Fix Solutions

Switch from binary to text format in COPY operations when length mismatches are suspected.
Use BIT VARYING(n) instead of BIT(n) when exact bit-length enforcement is not required.
Always stage external data into TEXT columns first, then cast and validate before inserting into fixed-length columns.
Align column definitions between source and target systems before running data migrations.

-- Check all fixed-length columns in your database
SELECT table_name, column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'public'
  AND data_type IN ('character', 'bit')
ORDER BY table_name;

Prevention Tips

Use staging tables with TEXT columns for all external data ingestion. Validate lengths programmatically before casting to fixed-length types. This decouples data loading from schema enforcement.
Document and centrally manage column length definitions across all integrated systems. When a schema change is needed, always audit downstream consumers and FDW mappings. Use information_schema.columns queries to automate schema drift detection in your CI/CD pipeline.

Related Errors

22001 – string_data_right_truncation: Data exceeds the maximum column length.
22P02 – invalid_text_representation: Invalid format when casting strings to specific types, often seen alongside 22026 with BIT types.
42804 – datatype_mismatch: Full type incompatibility, not just length.

Oracle ORA-02291 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 16 Aug 2026 10:01:27 +0000

ORA-02291: integrity constraint violated - parent key not found

ORA-02291 is one of the most common referential integrity errors in Oracle databases. It occurs when you attempt to INSERT or UPDATE a row in a child table with a foreign key value that does not exist in the referenced parent table. Simply put, Oracle is enforcing referential integrity by preventing "orphan" child records from being created.

Top 3 Causes

1. Inserting a Child Record with a Non-Existent Parent Key

The most frequent cause — trying to insert data into a child table while the referenced parent key simply doesn't exist yet.

-- Parent table: DEPARTMENTS
-- Child table: EMPLOYEES with FK on DEPT_ID

-- This will throw ORA-02291 if DEPT_ID 99 doesn't exist in DEPARTMENTS
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (5001, 'John Smith', 99);

-- Fix: Insert parent record first
INSERT INTO departments (dept_id, dept_name)
VALUES (99, 'New Division');

COMMIT;

-- Now the child insert will succeed
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (5001, 'John Smith', 99);

COMMIT;

2. Wrong Loading Order in Batch / ETL Jobs

In data migration or ETL pipelines, loading child tables before parent tables is a very common mistake that triggers this error at scale.

-- Check for orphan records in staging data BEFORE loading
SELECT s.order_id, s.customer_id
FROM orders_staging s
WHERE NOT EXISTS (
    SELECT 1
    FROM customers c
    WHERE c.customer_id = s.customer_id
);
-- If this returns rows, fix parent data first

-- Temporarily disable FK constraint for bulk load (use with caution)
ALTER TABLE orders DISABLE CONSTRAINT fk_orders_customer;

-- Perform bulk load
INSERT INTO orders SELECT * FROM orders_staging;

-- Re-enable after ensuring data integrity
ALTER TABLE orders ENABLE CONSTRAINT fk_orders_customer;

3. Data Type Mismatch or Whitespace Issues

Even if the parent key exists, a mismatch in data type, leading/trailing spaces, or case sensitivity can cause a lookup failure.

-- Parent stores '001' as VARCHAR2, child tries to insert numeric 1
-- This may fail due to implicit conversion issues
INSERT INTO order_lines (line_id, product_code)
VALUES (1, 1);  -- product_code '001' exists but numeric 1 does not match

-- Fix: Use explicit casting and TRIM
INSERT INTO order_lines (line_id, product_code)
VALUES (1, TRIM(TO_CHAR(1, '000')));  -- converts to '001'

-- Detect whitespace mismatches in parent table
SELECT '|' || product_code || '|' AS debug_value
FROM products
WHERE product_id = 100;

Quick Fix Solutions

-- 1. Verify the parent key exists before inserting
SELECT COUNT(*) FROM parent_table WHERE parent_id = :your_value;

-- 2. Find all orphan records causing issues
SELECT child.*
FROM child_table child
WHERE NOT EXISTS (
    SELECT 1 FROM parent_table p
    WHERE p.parent_id = child.parent_id
);

-- 3. Check FK constraint definition
SELECT constraint_name, r_constraint_name, status
FROM user_constraints
WHERE table_name = 'CHILD_TABLE'
AND constraint_type = 'R';

Prevention Tips

Validate before loading. Always run a pre-check query to confirm all foreign key values exist in the parent table before executing any bulk INSERT or migration script. Catching orphan records in staging is far cheaper than rolling back a failed production load.

Enforce load order. In ETL and batch jobs, always define a strict dependency-based loading sequence — parent tables must be fully committed before child tables are processed. Document this order and enforce it through your orchestration tool (e.g., Oracle Data Integrator, Apache Airflow).

-- Pre-migration validation template
SELECT 'FAILED - Orphan records exist' AS status, COUNT(*) AS cnt
FROM child_staging s
WHERE NOT EXISTS (SELECT 1 FROM parent_table p WHERE p.id = s.parent_id)
UNION ALL
SELECT 'OK - Ready to load', COUNT(*)
FROM child_staging s
WHERE EXISTS (SELECT 1 FROM parent_table p WHERE p.id = s.parent_id);

Related Errors

ORA-02292: Triggered when deleting a parent record that still has child records referencing it — the reverse of ORA-02291.
ORA-02298: Occurs when re-enabling a disabled FK constraint and existing data contains orphan child records.

PostgreSQL 2200H Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 16 Aug 2026 06:03:04 +0000

PostgreSQL Error 2200H: sequence generator limit exceeded

PostgreSQL error 2200H (sequence_generator_limit_exceeded) occurs when a sequence object has reached its defined maximum (or minimum) value and can no longer generate new numbers. This most commonly strikes SERIAL or SMALLSERIAL columns in high-volume tables, and it will bring your inserts to a grinding halt until resolved.

Top 3 Causes

1. INTEGER-based SERIAL column exhausted

The SERIAL type uses a 32-bit integer sequence with a maximum of 2,147,483,647. High-frequency inserts — even with frequent deletes — consume sequence values fast because rolled-back transactions still burn sequence numbers.

-- Check how close your sequence is to the limit
SELECT
    sequencename,
    last_value,
    max_value,
    ROUND((last_value::NUMERIC / max_value::NUMERIC) * 100, 2) AS usage_pct
FROM pg_sequences
WHERE sequencename = 'your_table_id_seq';

2. NO CYCLE default causes hard stop

By default, sequences are created with NO CYCLE, meaning once the max value is hit, every subsequent nextval() call throws error 2200H immediately.

-- Verify cycle setting on your sequence
SELECT sequencename, is_cycled, max_value, last_value
FROM pg_sequences
WHERE schemaname = 'public';

3. SMALLSERIAL used on a growing table

SMALLSERIAL tops out at just 32,767 — dangerously low for any table that grows over time. It's often chosen by mistake for log or event tables where row counts can explode unexpectedly.

-- Identify SMALLSERIAL / smallint sequences near their limit
SELECT
    sequencename,
    last_value,
    max_value,
    CASE WHEN max_value = 32767 THEN 'SMALLSERIAL ⚠️' ELSE 'OK' END AS type_warning
FROM pg_sequences
ORDER BY (last_value::NUMERIC / max_value::NUMERIC) DESC;

Quick Fix Solutions

Step 1 — Emergency patch: extend the sequence range

-- Immediately raise the max value to BIGINT range
ALTER SEQUENCE your_table_id_seq MAXVALUE 9223372036854775807;

Step 2 — Permanent fix: migrate column to BIGINT

-- Upgrade column type from INTEGER to BIGINT
ALTER TABLE your_table ALTER COLUMN id TYPE BIGINT;

-- For new tables, always use BIGSERIAL
CREATE TABLE better_table (
    id BIGSERIAL PRIMARY KEY,
    payload JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Modern alternative with IDENTITY columns (PostgreSQL 10+)
CREATE TABLE modern_table (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Step 3 — Resync sequence after data migration

-- Resync sequence to current max id to avoid conflicts
SELECT setval('your_table_id_seq', (SELECT MAX(id) FROM your_table));

Prevention Tips

1. Enforce BIGSERIAL as your team standard

Ban SERIAL and SMALLSERIAL in your DDL standards. Add a linting rule to your CI/CD pipeline that rejects any CREATE TABLE statement using those types. BIGSERIAL or BIGINT GENERATED ALWAYS AS IDENTITY should be the non-negotiable default for every primary key.

2. Monitor sequence usage proactively

Set up a monitoring job that alerts you when any sequence crosses 80% usage. Plug the query below into your monitoring stack (Prometheus, Datadog, pgBadger, etc.) and fire a Slack or PagerDuty alert before you hit the wall.

-- Sequences over 80% consumed — add this to your monitoring
SELECT
    schemaname || '.' || sequencename AS seq,
    last_value,
    max_value,
    ROUND((last_value::NUMERIC / max_value::NUMERIC) * 100, 2) AS pct_used
FROM pg_sequences
WHERE NOT is_cycled
  AND (last_value::NUMERIC / max_value::NUMERIC) >= 0.8
ORDER BY pct_used DESC;

Related Errors

Code	Name	Relationship
`22003`	`numeric_value_out_of_range`	Fires when the sequence value overflows the column's data type
`23505`	`unique_violation`	Occurs when a CYCLEd sequence reuses a value already in a unique column

Oracle ORA-02290 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 16 Aug 2026 06:01:34 +0000

ORA-02290: check constraint violated — Causes, Fixes & Prevention

ORA-02290 is thrown by Oracle Database when a INSERT or UPDATE statement attempts to store a value that violates a CHECK constraint defined on a table column. Oracle enforces these constraints to guarantee data integrity, and any DML that breaks the rule is automatically rolled back. Understanding which constraint was violated and why is the fastest path to resolution.

Top 3 Causes

1. Value Out of Allowed Range

The most common cause is inserting a numeric or date value that falls outside the range defined in the CHECK constraint.

-- Table definition with a CHECK constraint
CREATE TABLE employees (
    employee_id  NUMBER PRIMARY KEY,
    salary       NUMBER(10,2) CONSTRAINT chk_salary CHECK (salary > 0),
    age          NUMBER       CONSTRAINT chk_age    CHECK (age BETWEEN 18 AND 65)
);

-- This INSERT will raise ORA-02290 because salary = -500
INSERT INTO employees (employee_id, salary, age)
VALUES (101, -500, 30);

-- ORA-02290: check constraint (HR.CHK_SALARY) violated

-- Correct INSERT
INSERT INTO employees (employee_id, salary, age)
VALUES (101, 5000, 30);
COMMIT;

2. Disallowed String / Enumerated Value

CHECK constraints are frequently used to restrict a column to a fixed set of values (acting like an enum). Passing any value outside that set triggers ORA-02290.

-- CHECK constraint allowing only specific status values
ALTER TABLE orders
ADD CONSTRAINT chk_order_status
CHECK (status IN ('PENDING', 'CONFIRMED', 'SHIPPED', 'CANCELLED'));

-- This UPDATE will fail — 'PROCESSING' is not in the allowed list
UPDATE orders
SET    status = 'PROCESSING'
WHERE  order_id = 42;

-- ORA-02290: check constraint (OE.CHK_ORDER_STATUS) violated

-- First, identify what values are valid
SELECT constraint_name, search_condition
FROM   user_constraints
WHERE  constraint_name = 'CHK_ORDER_STATUS';

-- Then use a valid value
UPDATE orders
SET    status = 'CONFIRMED'
WHERE  order_id = 42;
COMMIT;

3. Dirty Data During Batch / Migration Jobs

During ETL processes or data migrations, source data often contains values that do not meet the target table's CHECK constraints, causing the entire batch to fail.

-- Pre-validate staging data BEFORE loading into the target table
SELECT COUNT(*) AS violation_count
FROM   orders_staging
WHERE  status NOT IN ('PENDING', 'CONFIRMED', 'SHIPPED', 'CANCELLED')
   OR  amount <= 0;

-- Cleanse data before inserting
INSERT INTO orders (order_id, status, amount)
SELECT order_id,
       CASE
           WHEN status IN ('PENDING','CONFIRMED','SHIPPED','CANCELLED')
               THEN status
           ELSE 'PENDING'   -- map unknown values to a safe default
       END,
       NULLIF(amount, 0)
FROM   orders_staging
WHERE  amount > 0;

COMMIT;

Quick Fix Solutions

Step 1 — Identify the violated constraint

-- Find all CHECK constraints on the target table
SELECT constraint_name,
       search_condition,
       status
FROM   user_constraints
WHERE  table_name      = 'EMPLOYEES'
  AND  constraint_type = 'C';

Step 2 — Modify the constraint if the business rule has changed

-- Drop the old constraint and recreate with updated rule
ALTER TABLE employees DROP CONSTRAINT chk_salary;

ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 0); -- now allows 0

-- Re-enable and validate
ALTER TABLE employees ENABLE VALIDATE CONSTRAINT chk_salary;

Step 3 — Temporarily disable during bulk load (use with caution)

-- Disable without validating existing rows
ALTER TABLE employees
DISABLE CONSTRAINT chk_salary NOVALIDATE;

-- Perform bulk insert ...

-- Re-enable and validate all rows
ALTER TABLE employees
ENABLE VALIDATE CONSTRAINT chk_salary;

Prevention Tips

Always pre-validate data before bulk DML. Run a SELECT COUNT(*) query against your staging table using the same condition as the CHECK constraint before executing the actual load. This catches violations early and avoids rolling back large transactions.

-- Generic pre-check pattern
SELECT *
FROM   employees_staging
WHERE  NOT (salary > 0)          -- mirrors CHECK condition
    OR NOT (age BETWEEN 18 AND 65);

Document and share CHECK constraints with the application team. Use the data dictionary to export all active constraints and include them in your project's technical spec so developers can mirror the validation logic in the application layer.

-- Export all CHECK constraints for documentation
SELECT table_name,
       constraint_name,
       search_condition,
       status
FROM   user_constraints
WHERE  constraint_type = 'C'
ORDER BY table_name;

Related Oracle Errors

Error Code	Description
ORA-02291	Foreign key constraint violated — parent key not found
ORA-02292	Foreign key constraint violated — child records exist
ORA-02293	Cannot enable CHECK constraint — existing data violates it
ORA-01400	Cannot insert NULL into a NOT NULL column
ORA-00001	UNIQUE constraint violated — duplicate value

PostgreSQL 22003 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 16 Aug 2026 00:02:53 +0000

PostgreSQL Error 22003: Numeric Value Out of Range

PostgreSQL error code 22003 (numeric_value_out_of_range) is thrown when you attempt to store or compute a value that exceeds the boundary of a numeric data type. This can happen during a simple INSERT, an UPDATE, or even inside an aggregate function like SUM(). Understanding which data type is overflowing — and why — is the fastest path to a fix.

Top 3 Causes

1. Integer Column Overflow

The most common cause in production systems is an auto-increment primary key or counter column reaching the maximum value of its integer type. INTEGER maxes out at 2,147,483,647, and high-traffic tables can hit this ceiling faster than expected.

-- Detect sequences nearing their limit
SELECT
  sequencename,
  last_value,
  max_value,
  ROUND((last_value::NUMERIC / max_value::NUMERIC) * 100, 2) AS usage_pct
FROM pg_sequences
ORDER BY usage_pct DESC;

-- Reproduce the error
CREATE TABLE demo (id INTEGER);
INSERT INTO demo VALUES (2147483647);
INSERT INTO demo VALUES (2147483648); -- ERROR: 22003

2. NUMERIC Precision Overflow

When a NUMERIC(precision, scale) column receives a value whose digits exceed the defined precision, PostgreSQL immediately raises 22003. For example, NUMERIC(6, 2) can hold a maximum of 9999.99 — any larger value will fail.

-- Reproduce the error
CREATE TABLE products (price NUMERIC(6, 2));
INSERT INTO products VALUES (9999.99);   -- OK
INSERT INTO products VALUES (10000.00);  -- ERROR: 22003

-- Identify out-of-range rows before migration
SELECT * FROM staging_products
WHERE price > 9999.99;

3. Arithmetic / Aggregate Overflow

A SUM() or multiplication operation on an INTEGER column can produce a result that overflows the column's type, even if every individual row value is perfectly valid. This is a silent danger in large batch reporting queries.

-- Dangerous: SUM result may overflow INTEGER
SELECT SUM(quantity) FROM order_items;  -- risky on large tables

-- Safe: cast to BIGINT before aggregating
SELECT SUM(quantity::BIGINT) AS total_quantity
FROM order_items;

-- Safe multiplication
SELECT
  (unit_price::NUMERIC(15,4) * quantity::NUMERIC(15,4))::NUMERIC(20,4)
    AS line_total
FROM order_items;

Quick Fix Solutions

-- Fix 1: Widen an integer column
ALTER TABLE users ALTER COLUMN user_id TYPE BIGINT;

-- Fix 2: Expand NUMERIC precision
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(15, 2);

-- Fix 3: Use BIGINT / BIGSERIAL for new tables (best practice)
CREATE TABLE new_orders (
  order_id   BIGSERIAL PRIMARY KEY,
  amount     NUMERIC(15, 2),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Fix 4: Handle error gracefully in PL/pgSQL
DO $$
BEGIN
  INSERT INTO demo VALUES (2147483648);
EXCEPTION
  WHEN numeric_value_out_of_range THEN
    RAISE NOTICE 'Value out of range — skipped.';
END;
$$;

Prevention Tips

Always use BIGINT/BIGSERIAL for primary keys and counters. There is almost no downside to using BIGINT over INTEGER in modern hardware, and it eliminates the most common source of 22003 errors entirely.

Add CHECK constraints and monitor sequences regularly. Enforce value boundaries at the database level rather than relying solely on application logic. Schedule a daily job to alert you when any sequence exceeds 80% utilization — catching the problem before it becomes an outage.

-- Proactive CHECK constraint
ALTER TABLE products
  ADD CONSTRAINT chk_price_positive CHECK (price >= 0);

-- Daily sequence health check
SELECT sequencename, last_value, max_value,
  ROUND((last_value::NUMERIC / max_value::NUMERIC) * 100, 2) AS pct_used
FROM pg_sequences
WHERE (last_value::NUMERIC / max_value::NUMERIC) > 0.8;

Oracle ORA-02289 Error: Causes and Solutions Complete Guide

umzzil nng — Sun, 16 Aug 2026 00:01:23 +0000

ORA-02289: sequence does not exist — Causes, Fixes & Prevention

ORA-02289 is thrown by Oracle Database when a session attempts to reference a sequence object that doesn't exist in the accessible schema. This can happen during NEXTVAL/CURRVAL calls, or when executing DDL statements like DROP SEQUENCE or ALTER SEQUENCE against a non-existent sequence. It's one of the most common deployment-related errors in Oracle environments.

Top 3 Causes

1. The Sequence Simply Doesn't Exist (Never Created or Dropped)

The most frequent cause — the sequence was never deployed to the target environment, or it was accidentally dropped.

-- Check if the sequence exists in your schema
SELECT SEQUENCE_NAME, LAST_NUMBER
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'ORDER_SEQ';

-- Check across all schemas (requires DBA privilege)
SELECT SEQUENCE_OWNER, SEQUENCE_NAME
FROM DBA_SEQUENCES
WHERE SEQUENCE_NAME = 'ORDER_SEQ';

-- If missing, recreate it safely
DECLARE
    v_start NUMBER;
BEGIN
    SELECT NVL(MAX(ORDER_ID), 0) + 1 INTO v_start FROM ORDERS;
    EXECUTE IMMEDIATE
        'CREATE SEQUENCE ORDER_SEQ START WITH ' || v_start ||
        ' INCREMENT BY 1 NOCYCLE NOCACHE';
END;
/

2. Wrong Schema — Missing Privileges or Synonym

Oracle resolves object names based on the current session's schema. If ORDER_SEQ belongs to SCOTT but you're connected as APP_USER without a synonym or explicit schema prefix, ORA-02289 will fire.

-- Option A: Use fully qualified schema.sequence reference
SELECT SCOTT.ORDER_SEQ.NEXTVAL FROM DUAL;

-- Option B: Grant privilege and create a private synonym
GRANT SELECT ON SCOTT.ORDER_SEQ TO APP_USER;

CREATE SYNONYM ORDER_SEQ FOR SCOTT.ORDER_SEQ;

-- Now this works without schema prefix
SELECT ORDER_SEQ.NEXTVAL FROM DUAL;

3. Case-Sensitive Name or Typo

If a sequence was created with double quotes, Oracle stores it with exact case. Calling it without quotes causes ORA-02289 because Oracle uppercases unquoted identifiers by default.

-- Sequence created with double quotes (case-sensitive)
CREATE SEQUENCE "mySeq" START WITH 1 INCREMENT BY 1;

-- WRONG — Oracle looks for MYSEQ, not mySeq
SELECT mySeq.NEXTVAL FROM DUAL;  -- ORA-02289

-- CORRECT — must use double quotes
SELECT "mySeq".NEXTVAL FROM DUAL;

-- Best practice: avoid quoted identifiers entirely
DROP SEQUENCE "mySeq";
CREATE SEQUENCE MYSEQ START WITH 1 INCREMENT BY 1;
SELECT MYSEQ.NEXTVAL FROM DUAL;

Quick Fix Checklist

-- 1. Verify the exact sequence name (watch for typos)
SELECT OBJECT_NAME, STATUS
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'SEQUENCE'
  AND OBJECT_NAME LIKE '%SEQ%';

-- 2. Confirm current user and schema context
SELECT USER, SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;

-- 3. List all sequences visible to current user
SELECT SEQUENCE_NAME FROM USER_SEQUENCES ORDER BY 1;

Prevention Tips

Automate pre-deployment validation. Add a sequence existence check to your CI/CD pipeline before any release. A simple script that queries USER_SEQUENCES or DBA_SEQUENCES for all required objects can catch missing sequences before they hit production.

-- Pre-deployment validation query
SELECT 'ORDER_SEQ' AS SEQ, COUNT(*) AS FOUND FROM USER_SEQUENCES WHERE SEQUENCE_NAME='ORDER_SEQ'
UNION ALL
SELECT 'CUSTOMER_SEQ', COUNT(*) FROM USER_SEQUENCES WHERE SEQUENCE_NAME='CUSTOMER_SEQ';
-- Any row with FOUND=0 needs immediate attention before deployment.

Standardize naming conventions and version-control all DDL. Always use uppercase, unquoted sequence names following a consistent pattern like TABLENAME_SEQ. Store all DDL scripts in Git or a similar version control system so any environment can be rebuilt reliably. This eliminates the "it exists in dev but not in prod" class of errors permanently.

PostgreSQL 22002 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 15 Aug 2026 14:02:52 +0000

PostgreSQL Error 22002: null value no indicator parameter

PostgreSQL error code 22002 (null_value_no_indicator_parameter) occurs when a query returns a NULL value into a host variable that has no associated indicator variable to handle it. This is most commonly seen in embedded SQL environments (ECPG), ODBC-based applications, or legacy C/C++ code that interacts directly with PostgreSQL. Without an indicator variable, the runtime has no safe place to signal that a NULL was received, so PostgreSQL raises this error to prevent silent data corruption.

Top 3 Causes

1. Missing Indicator Variable in ECPG (Embedded SQL in C)

When using ECPG, every host variable that could receive a NULL must have a paired indicator variable. Omitting it triggers error 22002 as soon as a NULL is fetched.

-- Problematic: No indicator variable declared
EXEC SQL SELECT salary INTO :emp_salary FROM employees WHERE emp_id = :id;

-- Correct: Declare and use an indicator variable
EXEC SQL BEGIN DECLARE SECTION;
    int emp_salary;
    short emp_salary_ind;  /* -1 = NULL, 0 = valid value */
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT salary
         INTO :emp_salary INDICATOR :emp_salary_ind
         FROM employees WHERE emp_id = :id;

-- Then check in C code:
-- if (emp_salary_ind == -1) { /* handle NULL */ }

2. Query Returns NULL from a Nullable Column

If a column has no NOT NULL constraint, it can contain NULL at any time. Code written under the assumption that data always exists will break in production when real NULL values appear.

-- This column allows NULL — dangerous without an indicator
SELECT bonus FROM employees WHERE emp_id = 1001;

-- Safer: Use COALESCE to substitute a default value
SELECT COALESCE(bonus, 0.00) AS bonus
FROM employees WHERE emp_id = 1001;

-- Multi-column example
SELECT
    emp_id,
    COALESCE(first_name, '')        AS first_name,
    COALESCE(salary, 0)             AS salary,
    COALESCE(department, 'N/A')     AS department
FROM employees
WHERE hire_date >= '2023-01-01';

3. Poor Table Design — No NOT NULL Constraints or Defaults

Tables created without explicit NOT NULL constraints and DEFAULT values are silent time bombs. Any insert that omits a column will store NULL, eventually triggering 22002 in connected applications.

-- Risky table design
CREATE TABLE orders (
    order_id   SERIAL PRIMARY KEY,
    customer   VARCHAR(100),   -- NULL allowed by default!
    total      NUMERIC(12,2)   -- NULL allowed by default!
);

-- Safe table design with constraints and defaults
CREATE TABLE orders (
    order_id   SERIAL PRIMARY KEY,
    customer   VARCHAR(100)  NOT NULL DEFAULT 'Guest',
    total      NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- Audit existing tables for unexpected NULLable columns
SELECT column_name, is_nullable, column_default
FROM information_schema.columns
WHERE table_name   = 'orders'
  AND table_schema = 'public'
ORDER BY ordinal_position;

Quick Fix Solutions

Option A — Add COALESCE to your query (fastest fix for legacy code):

SELECT COALESCE(salary, 0), COALESCE(notes, '') FROM employees;

Option B — Add NOT NULL + DEFAULT to existing columns:

ALTER TABLE employees
    ALTER COLUMN salary SET NOT NULL,
    ALTER COLUMN salary SET DEFAULT 0;

Option C — Find all NULLs before they hit your app:

SELECT COUNT(*) FILTER (WHERE salary IS NULL)   AS null_salary,
       COUNT(*) FILTER (WHERE bonus  IS NULL)   AS null_bonus
FROM employees;

Prevention Tips

Always declare indicator variables in ECPG/ODBC. Make it a mandatory code-review checklist item for any embedded SQL or ODBC code that fetches data. Never bind a host variable to a nullable column without a corresponding indicator buffer.
Enforce NOT NULL + DEFAULT at the schema level. During table design, explicitly decide whether each column should allow NULL. If not, add NOT NULL DEFAULT <value> immediately. Run periodic audits using information_schema.columns to catch nullable columns that sneak in over time.

Related Errors

Code	Name	Brief Description
22001	`string_data_right_truncation`	String too long for target column
22003	`numeric_value_out_of_range`	Number exceeds target type range
22004	`null_value_not_allowed`	NULL not permitted in this context
42804	`datatype_mismatch`	Host variable type doesn't match column type

Oracle ORA-02270 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 15 Aug 2026 14:01:20 +0000

ORA-02270: No Matching Unique or Primary Key for This Column-List

ORA-02270 occurs when you try to create a foreign key constraint that references a column (or set of columns) in a parent table that does not have a corresponding Primary Key or Unique Key defined. Oracle strictly enforces referential integrity, meaning every foreign key must point to a uniquely identifiable set of columns in the parent table. This error is most commonly encountered during DDL operations such as CREATE TABLE or ALTER TABLE.

Top 3 Causes and Fixes

Cause 1: No Primary Key or Unique Key on the Referenced Column

The most common cause is simply that the parent table's referenced column lacks a PRIMARY KEY or UNIQUE constraint. A regular index alone is not sufficient.

-- This will cause ORA-02270 if parent_table.parent_id has no PK or UNIQUE constraint
ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id)
REFERENCES parent_table(parent_id);  -- ORA-02270 if no PK/UK on parent_id

-- Fix: Add a Primary Key to the parent table first
ALTER TABLE parent_table
ADD CONSTRAINT pk_parent PRIMARY KEY (parent_id);

-- Now the foreign key creation will succeed
ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id)
REFERENCES parent_table(parent_id);

Cause 2: Composite Key Column Order Mismatch

When referencing a composite Primary Key or Unique Key, the column list in the foreign key must exactly match the order and composition of the parent key.

-- Parent table has a composite PK on (dept_id, emp_type) in this order
CREATE TABLE parent_table (
    dept_id   NUMBER,
    emp_type  VARCHAR2(10),
    CONSTRAINT pk_parent PRIMARY KEY (dept_id, emp_type)
);

-- WRONG: Column order is reversed — causes ORA-02270
ALTER TABLE child_table
ADD CONSTRAINT fk_wrong
FOREIGN KEY (emp_type, dept_id)
REFERENCES parent_table(emp_type, dept_id);

-- CORRECT: Match the exact column order of the parent PK
ALTER TABLE child_table
ADD CONSTRAINT fk_correct
FOREIGN KEY (dept_id, emp_type)
REFERENCES parent_table(dept_id, emp_type);

Cause 3: Typo in Table/Column Name or Wrong Schema Reference

If the parent table name, column name, or schema is misspelled, Oracle either cannot find the object or finds it without a matching key constraint.

-- Verify the parent table and its constraints before creating the FK
SELECT owner, constraint_name, constraint_type, table_name
FROM all_constraints
WHERE table_name = 'PARENT_TABLE'
  AND constraint_type IN ('P', 'U');

-- Check column details of the constraint
SELECT ucc.constraint_name, ucc.column_name, ucc.position
FROM all_cons_columns ucc
JOIN all_constraints uc ON ucc.constraint_name = uc.constraint_name
WHERE uc.table_name = 'PARENT_TABLE'
  AND uc.constraint_type IN ('P', 'U')
ORDER BY ucc.position;

-- Cross-schema foreign key: always specify the schema explicitly
ALTER TABLE myschema.child_table
ADD CONSTRAINT fk_cross
FOREIGN KEY (dept_id)
REFERENCES hrschema.parent_table(dept_id);

Quick Fix Checklist

Verify the parent table has a PK or UK on the referenced column(s) using USER_CONSTRAINTS or ALL_CONSTRAINTS.
Check composite key column order — it must match exactly between parent and child.
Confirm table and column names are spelled correctly and the schema is accessible.
Ensure data types match between the foreign key columns and the parent key columns.

Prevention Tips

Always define Primary Keys at table creation time. Establish a DDL coding standard in your organization that requires PRIMARY KEY constraints to be declared in the CREATE TABLE statement, never deferred.
Use a pre-deployment validation script to verify all parent table constraints exist before running FK creation scripts, reducing the chance of ORA-02270 in production.

-- Quick pre-check before adding a foreign key
SELECT COUNT(*) AS pk_uk_count
FROM user_constraints
WHERE table_name = 'PARENT_TABLE'
  AND constraint_type IN ('P', 'U');
-- If result is 0, add the required constraint before proceeding

PostgreSQL 22004 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 15 Aug 2026 10:02:56 +0000

PostgreSQL Error 22004: null value not allowed

PostgreSQL error code 22004 (null_value_not_allowed) is raised when a NULL value is passed to a context that explicitly prohibits it — such as a NOT NULL column, a domain type with a null restriction, or a function parameter that does not accept NULL. Unlike the more common 23502 (not_null_violation), this error often surfaces in function calls and domain-level constraints, making it slightly trickier to diagnose. Understanding the root cause quickly is essential to maintaining data integrity and service stability.

Top 3 Causes

1. Inserting NULL into a NOT NULL Column

The most frequent cause is attempting to insert or update a NOT NULL column with a NULL value, either explicitly or by omitting the column in an INSERT statement.

-- Table definition
CREATE TABLE employees (
    emp_id   SERIAL PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    dept_id  INTEGER      NOT NULL
);

-- This will raise an error
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (1, 'Alice', NULL);
-- ERROR: null value in column "dept_id" violates not-null constraint

-- Fix: provide a valid value or use COALESCE
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (1, 'Alice', COALESCE(NULL, 0));

-- Or set a default at the schema level
ALTER TABLE employees
    ALTER COLUMN dept_id SET DEFAULT 0;

2. Passing NULL to a STRICT Function

When a PostgreSQL function is defined with the STRICT keyword, any NULL argument causes the function to short-circuit and return NULL immediately. In some contexts — particularly inside PL/pgSQL blocks that expect a non-null result — this can trigger error 22004.

-- STRICT function example
CREATE OR REPLACE FUNCTION calculate_tax(amount NUMERIC, rate NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
    RETURN amount * rate;
END;
$$;

-- Calling with NULL silently returns NULL due to STRICT
SELECT calculate_tax(500, NULL); -- returns NULL

-- Fix: Remove STRICT and handle NULL explicitly
CREATE OR REPLACE FUNCTION calculate_tax_safe(amount NUMERIC, rate NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    IF amount IS NULL OR rate IS NULL THEN
        RAISE EXCEPTION 'Arguments must not be NULL'
            USING ERRCODE = '22004';
    END IF;
    RETURN amount * rate;
END;
$$;

3. Domain Type with NOT NULL Constraint

PostgreSQL allows user-defined domain types to carry a NOT NULL constraint. Inserting NULL into a column using such a domain type raises 22004, which can be confusing if developers are unaware of the domain definition.

-- Define a domain with NOT NULL
CREATE DOMAIN positive_amount AS NUMERIC
    NOT NULL
    CHECK (VALUE > 0);

-- Use it in a table
CREATE TABLE invoices (
    invoice_id SERIAL PRIMARY KEY,
    amount     positive_amount
);

-- This fails with error 22004
INSERT INTO invoices (invoice_id, amount)
VALUES (1, NULL);
-- ERROR: domain positive_amount does not allow null values

-- Fix: Replace NULL with a default value
INSERT INTO invoices (invoice_id, amount)
VALUES (1, COALESCE(NULL, 0.01));

-- Or redefine the domain without NOT NULL
DROP DOMAIN positive_amount CASCADE;
CREATE DOMAIN positive_amount AS NUMERIC
    CHECK (VALUE > 0);

Quick Fix Solutions

-- 1. Check which columns are NOT NULL with no default
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
  AND is_nullable = 'NO'
  AND column_default IS NULL;

-- 2. Use COALESCE to substitute NULLs before inserting
INSERT INTO orders (order_id, customer_id, status)
SELECT
    order_id,
    COALESCE(customer_id, 0),
    COALESCE(status, 'unknown')
FROM staging_orders;

-- 3. Add a DEFAULT to an existing NOT NULL column
ALTER TABLE orders
    ALTER COLUMN customer_id SET DEFAULT 0;

Prevention Tips

1. Always pair NOT NULL with a DEFAULT value during table design. This prevents accidental NULL insertions when columns are omitted in INSERT statements, and makes your schema self-documenting.

-- Recommended table design
CREATE TABLE orders (
    order_id    SERIAL       PRIMARY KEY,
    customer_id INTEGER      NOT NULL DEFAULT 0,
    status      VARCHAR(20)  NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

2. Validate NULLs at the application layer before hitting the database. Treat the DB constraint as a last line of defense, not the first. Use input validation in your API or service layer to catch NULL values early, reducing round-trips and improving error messages for end users.

Related Errors

23502 (not_null_violation) — The closest sibling; triggered by NOT NULL column violations in standard DML operations.
22023 (invalid_parameter_value) — Raised when a function receives a parameter value that is technically non-null but still invalid in context.

Oracle ORA-02266 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 15 Aug 2026 10:01:21 +0000

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

ORA-02266 occurs when you attempt to perform a DDL operation—such as TRUNCATE TABLE, DROP TABLE, or disabling a primary key constraint—on a parent table whose primary or unique key is still referenced by an enabled foreign key in a child table. Oracle enforces this restriction to protect referential integrity. Until the referencing foreign key constraints are handled, the operation will be blocked.

Top 3 Causes

1. TRUNCATE TABLE Blocked by Active Foreign Keys

Unlike DELETE, TRUNCATE is a DDL statement and cannot be rolled back, so Oracle enforces stricter referential integrity checks. If any child table has an enabled FK pointing to the parent, the truncate fails immediately.

-- This will raise ORA-02266 if child_table has an active FK
TRUNCATE TABLE parent_table;

-- Fix: Disable the FK first, truncate, then re-enable
ALTER TABLE child_table DISABLE CONSTRAINT fk_child_parent;
TRUNCATE TABLE parent_table;
ALTER TABLE child_table ENABLE CONSTRAINT fk_child_parent;

2. DROP TABLE Without CASCADE CONSTRAINTS

Dropping a parent table while child tables still hold enabled foreign key references will trigger ORA-02266. This commonly happens during schema redesigns or migrations when dependency order is not respected.

-- This will fail if FK references exist
DROP TABLE parent_table;

-- Fix: Use CASCADE CONSTRAINTS to auto-drop referencing FKs
-- Note: child TABLE is NOT dropped, only the FK constraints are removed
DROP TABLE parent_table CASCADE CONSTRAINTS;

-- Check what FKs reference your table before dropping
SELECT c.table_name    AS child_table,
       c.constraint_name AS fk_name
  FROM user_constraints c
  JOIN user_constraints p
    ON c.r_constraint_name = p.constraint_name
 WHERE p.table_name       = 'PARENT_TABLE'
   AND c.constraint_type  = 'R';

3. Disabling or Dropping a Primary Key With Active Child FKs

Attempting to disable or drop the primary key on a parent table when child FKs are still enabled will also produce this error. The CASCADE option on ALTER TABLE resolves this cleanly.

-- This will fail if a child FK is enabled
ALTER TABLE parent_table DISABLE PRIMARY KEY;

-- Fix: Use CASCADE to disable child FKs simultaneously
ALTER TABLE parent_table DISABLE PRIMARY KEY CASCADE;

-- Re-enable PK after your work is done
ALTER TABLE parent_table ENABLE PRIMARY KEY;

-- Then re-enable child FK constraints individually or via dynamic SQL
BEGIN
  FOR r IN (
    SELECT c.table_name, c.constraint_name
      FROM user_constraints c
      JOIN user_constraints p
        ON c.r_constraint_name = p.constraint_name
     WHERE p.table_name      = 'PARENT_TABLE'
       AND c.constraint_type = 'R'
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER TABLE ' || r.table_name ||
      ' ENABLE CONSTRAINT ' || r.constraint_name;
  END LOOP;
END;
/

Quick Fix Summary

Scenario	Solution
`TRUNCATE` blocked	Disable FK → Truncate → Enable FK
`DROP TABLE` blocked	Use `CASCADE CONSTRAINTS`
`DISABLE PRIMARY KEY` blocked	Use `DISABLE PRIMARY KEY CASCADE`

Prevention Tips

1. Always audit foreign key dependencies before DDL operations.
Run a dependency check query against user_constraints or dba_constraints before any DDL on a parent table. Make this a mandatory step in your deployment checklist.

-- Quick dependency check
SELECT c.table_name, c.constraint_name, c.status
  FROM user_constraints c
  JOIN user_constraints p ON c.r_constraint_name = p.constraint_name
 WHERE p.table_name = 'YOUR_TABLE'
   AND c.constraint_type = 'R';

2. Standardize a FK-safe data purge script for recurring batch jobs.
For any recurring truncate or bulk-delete operation, wrap the logic in a reusable script that handles FK disable/enable automatically. This eliminates ORA-02266 as a runtime surprise and keeps your operations clean and auditable.

PostgreSQL 2200G Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 15 Aug 2026 06:02:58 +0000

PostgreSQL Error 2200G: most specific type mismatch

PostgreSQL error 2200G: most specific type mismatch occurs when the database engine cannot resolve a single, unambiguous "most specific type" among values being processed together in a type-sensitive context. This error commonly surfaces in XML functions, UNION queries involving domain or composite types, and overloaded function calls where type resolution becomes ambiguous. Understanding and resolving this error requires explicit type casting and a solid grasp of PostgreSQL's type hierarchy system.

Top 3 Causes

1. XML Functions with Mixed Types

PostgreSQL XML functions like XMLFOREST and XMLELEMENT require consistent types across their arguments. When you pass values of different types without explicit casting, the engine fails to determine the most specific common type.

-- Problematic query: mixing INTEGER, TEXT, NUMERIC
SELECT XMLFOREST(
    employee_id,    -- INTEGER
    department,     -- TEXT
    salary          -- NUMERIC
)
FROM employees;
-- ERROR: 2200G most specific type mismatch

-- Fix: explicitly cast all values to TEXT
SELECT XMLFOREST(
    employee_id::TEXT AS "employeeId",
    department::TEXT AS "department",
    salary::TEXT AS "salary"
)
FROM employees;

2. UNION Queries with Incompatible Column Types

When combining result sets using UNION or UNION ALL, corresponding columns must share a resolvable common type. Domain types or composite types with no clear hierarchy cause the 2200G error.

-- Problematic: DATE vs TIMESTAMP in the same column position
SELECT order_id, customer_name, created_at::DATE AS event_date
FROM orders
UNION ALL
SELECT product_id, product_name, updated_at::TIMESTAMP AS event_date
FROM products;
-- ERROR: 2200G most specific type mismatch

-- Fix: cast both columns to the same type
SELECT order_id, customer_name, created_at::TIMESTAMP AS event_date
FROM orders
UNION ALL
SELECT product_id, product_name, updated_at::TIMESTAMP AS event_date
FROM products;

3. Ambiguous Function Overloading Resolution

PostgreSQL supports function overloading, but when argument types match multiple signatures and no single "most specific" version can be determined, error 2200G is raised.

-- Two overloaded functions
CREATE FUNCTION calc(val INTEGER) RETURNS TEXT AS $$
    SELECT 'int:' || val::TEXT;
$$ LANGUAGE SQL;

CREATE FUNCTION calc(val NUMERIC) RETURNS TEXT AS $$
    SELECT 'num:' || val::TEXT;
$$ LANGUAGE SQL;

-- Ambiguous call
SELECT calc(100);  -- May raise 2200G

-- Fix: use explicit casting to target the correct overload
SELECT calc(100::INTEGER);   -- Resolves to INTEGER version
SELECT calc(100::NUMERIC);   -- Resolves to NUMERIC version

-- Verify available overloads
SELECT proname, pg_get_function_arguments(oid) AS args
FROM pg_proc
WHERE proname = 'calc';

Quick Fix Solutions

Always cast explicitly — never rely on implicit type coercion in XML functions, UNION queries, or overloaded function calls.
Use pg_typeof() to inspect actual column types before building complex queries.
Normalize types at the schema level to avoid domain type conflicts in set operations.

-- Inspect column types before writing complex queries
SELECT column_name, data_type, udt_name
FROM information_schema.columns
WHERE table_name = 'employees'
ORDER BY ordinal_position;

-- Use pg_typeof() inline for quick type checking
SELECT pg_typeof(employee_id), pg_typeof(salary)
FROM employees
LIMIT 1;

Prevention Tips

Enforce explicit casting conventions in your team's SQL style guide. Require ::type or CAST(x AS type) in all XML-related, UNION, and function call code. Include type consistency checks in your code review checklist.
Automate type validation in CI/CD by running schema and query type checks before deployment. Use pg_typeof() in unit tests to assert expected return types and catch mismatches early in the development cycle.

-- Simple type assertion pattern for automated testing
DO $$
DECLARE v_type TEXT;
BEGIN
    SELECT pg_typeof(salary)::TEXT INTO v_type FROM employees LIMIT 1;
    IF v_type != 'numeric' THEN
        RAISE EXCEPTION 'Type check failed: expected numeric, got %', v_type;
    END IF;
    RAISE NOTICE 'Type check passed: %', v_type;
END;
$$;

Oracle ORA-02264 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 15 Aug 2026 06:01:25 +0000

ORA-02264: name already used by an existing constraint

ORA-02264 occurs when you attempt to create or add a constraint using a name that already exists within the same Oracle schema. Unlike many other databases, Oracle enforces constraint name uniqueness at the schema level, not the table level — meaning even two different tables cannot share the same constraint name within one schema. This error is commonly triggered during script re-execution, migrations, or deployments where idempotency is not handled properly.

Top 3 Causes

1. Duplicate Constraint Name Across Tables in the Same Schema

Oracle requires all constraint names to be unique within a schema, regardless of which table they belong to.

-- This will fail if PK_EMPLOYEES already exists anywhere in the schema
ALTER TABLE contractors
ADD CONSTRAINT PK_EMPLOYEES PRIMARY KEY (contractor_id);
-- ERROR: ORA-02264: name already used by an existing constraint

-- Fix: Check existing constraints first
SELECT constraint_name, table_name
FROM user_constraints
WHERE constraint_name = 'PK_EMPLOYEES';

-- Then use a unique name
ALTER TABLE contractors
ADD CONSTRAINT PK_CONTRACTORS PRIMARY KEY (contractor_id);

2. Re-running DDL Scripts Without Idempotency Checks

In automated deployments or CI/CD pipelines, DDL scripts are sometimes executed more than once. If the constraint was already created in the first run, the second run will fail with ORA-02264.

-- Idempotent approach using PL/SQL
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM user_constraints
  WHERE constraint_name = 'PK_ORDERS';

  IF v_count = 0 THEN
    EXECUTE IMMEDIATE
      'ALTER TABLE orders ADD CONSTRAINT PK_ORDERS PRIMARY KEY (order_id)';
    DBMS_OUTPUT.PUT_LINE('Constraint created successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Constraint already exists. Skipping.');
  END IF;
END;
/

3. Attempting to Recreate a Constraint Without Dropping It First

Oracle does not support directly modifying a constraint. If you try to re-add a constraint with the same name (e.g., to change its definition), it will fail unless the original is dropped first.

-- WRONG: Will throw ORA-02264
ALTER TABLE employees
ADD CONSTRAINT PK_EMPLOYEES PRIMARY KEY (employee_id);

-- CORRECT: Drop first, then re-add
ALTER TABLE employees DROP CONSTRAINT PK_EMPLOYEES;

ALTER TABLE employees
ADD CONSTRAINT PK_EMPLOYEES PRIMARY KEY (employee_id)
USING INDEX TABLESPACE INDX;

-- If child FK constraints exist, use CASCADE
ALTER TABLE employees DROP CONSTRAINT PK_EMPLOYEES CASCADE;

Quick Fix Solutions

-- 1. Find all constraints with a specific name
SELECT constraint_name, table_name, constraint_type, status
FROM user_constraints
WHERE constraint_name = 'YOUR_CONSTRAINT_NAME';

-- 2. List all constraints for a table
SELECT constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name = 'YOUR_TABLE_NAME';

-- 3. Rename a constraint (Oracle 12c+)
ALTER TABLE employees
RENAME CONSTRAINT PK_EMPLOYEES TO PK_EMPLOYEES_V2;

Prevention Tips

Adopt a strict naming convention to avoid collisions across tables in the same schema. A reliable pattern is {TYPE}_{TABLE}_{COLUMN}:

-- Recommended naming convention
-- PK_{TABLE_NAME}
-- FK_{TABLE_NAME}_{REF_TABLE}
-- UK_{TABLE_NAME}_{COLUMN}
-- CK_{TABLE_NAME}_{COLUMN}

ALTER TABLE invoices ADD CONSTRAINT PK_INVOICES PRIMARY KEY (invoice_id);
ALTER TABLE invoices ADD CONSTRAINT FK_INVOICES_ORDERS FOREIGN KEY (order_id) REFERENCES orders(order_id);
ALTER TABLE invoices ADD CONSTRAINT CK_INVOICES_STATUS CHECK (status IN ('DRAFT','SENT','PAID'));

Always validate before deploying by running a pre-flight check script to detect naming conflicts before any DDL is executed in production:

-- Pre-deployment validation
SELECT constraint_name, table_name
FROM user_constraints
WHERE constraint_name IN (
  'PK_INVOICES',
  'FK_INVOICES_ORDERS',
  'CK_INVOICES_STATUS'
);
-- If any rows are returned, resolve conflicts before proceeding.

Related Oracle Errors

Error Code	Description
ORA-02260	Table can have only one primary key
ORA-02261	Unique or primary key already exists on the table
ORA-02275	Referential constraint already exists on the table
ORA-00001	Unique constraint violated (runtime DML error)