DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-02261 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02261: Such Unique or Primary Key Already Exists in the Table

ORA-02261 is thrown by Oracle when you attempt to add a UNIQUE or PRIMARY KEY constraint to a table that already has an identical constraint defined on the same column or combination of columns. Oracle enforces that each table can hold only one PRIMARY KEY, and duplicate UNIQUE constraints covering the same columns are not permitted. This error most commonly appears during repeated DDL script executions, database migrations, or when ORM frameworks attempt to auto-generate schema constraints without checking existing definitions.

Top 3 Causes

1. Running the Same DDL Script More Than Once

The most frequent cause in real-world environments is executing a deployment script that adds constraints without first verifying whether they already exist.

-- This will fail on the second run with ORA-02261
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);

-- Check existing constraints first
SELECT constraint_name, constraint_type
FROM   user_constraints
WHERE  table_name = 'EMPLOYEES';
Enter fullscreen mode Exit fullscreen mode

2. Adding a PRIMARY KEY When One Already Exists

A table can have exactly one PRIMARY KEY. Attempting to add another — even with a different constraint name — triggers ORA-02261 immediately.

-- First PRIMARY KEY already exists:
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);

-- This second attempt causes ORA-02261:
ALTER TABLE employees
ADD CONSTRAINT pk_emp_new PRIMARY KEY (employee_id);

-- Correct approach: drop the old one first
ALTER TABLE employees DROP CONSTRAINT pk_employees;

ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);
Enter fullscreen mode Exit fullscreen mode

3. Duplicate UNIQUE Constraints on the Same Columns

Even if constraint names differ, Oracle will raise ORA-02261 if a new UNIQUE constraint targets a column set that is already covered by an existing UNIQUE or PRIMARY KEY constraint.

-- Existing unique constraint on email column
ALTER TABLE employees
ADD CONSTRAINT uq_emp_email UNIQUE (email);

-- The following fails — same column already has a unique constraint
ALTER TABLE employees
ADD CONSTRAINT uq_email_v2 UNIQUE (email);  -- ORA-02261

-- Verify before adding
SELECT uc.constraint_name, ucc.column_name
FROM   user_constraints  uc
JOIN   user_cons_columns ucc
       ON uc.constraint_name = ucc.constraint_name
WHERE  uc.table_name      = 'EMPLOYEES'
AND    uc.constraint_type IN ('P', 'U');
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Use an idempotent PL/SQL block to safely add constraints without risking ORA-02261:

-- Safe, re-runnable constraint addition
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO   v_count
  FROM   user_constraints
  WHERE  table_name      = 'EMPLOYEES'
  AND    constraint_type = 'P';

  IF v_count = 0 THEN
    EXECUTE IMMEDIATE
      'ALTER TABLE employees ADD CONSTRAINT pk_employees
       PRIMARY KEY (employee_id)';
    DBMS_OUTPUT.PUT_LINE('Primary key created successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Primary key already exists. Skipping.');
  END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always check before you add. Query USER_CONSTRAINTS or ALL_CONSTRAINTS before any DDL that creates constraints. Wrap all schema change scripts in idempotent PL/SQL blocks, especially in CI/CD pipelines where scripts may run multiple times.

2. Use a schema versioning tool. Tools like Flyway or Liquibase track which changesets have already been applied, preventing duplicate executions. Establish a clear naming convention (e.g., PK_TABLENAME, UQ_TABLENAME_COLNAME) to make duplicates easy to spot during code review.

Related Errors

  • ORA-02260 — table can have only one primary key: Raised specifically when a second PRIMARY KEY is added.
  • ORA-02264 — name already used by an existing constraint: Triggered when the constraint name itself is duplicated.
  • ORA-00955 — name is already used by an existing object: Occurs when the constraint name conflicts with another schema object.
  • ORA-01408 — such column list already indexed: Related error when a duplicate index is created on the same column set.

📖 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.

Top comments (0)