Oracle ORA-02264 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

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)

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community