Oracle ORA-02260 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02260: Table Can Have Only One Primary Key

ORA-02260 is an Oracle database error that occurs when you attempt to define more than one primary key constraint on a single table. Since relational database theory mandates that each table must have at most one primary key, Oracle enforces this rule strictly at the DDL level. This error commonly surfaces during deployment script re-runs, migration tasks, or careless table design.

Top 3 Causes and SQL Examples

1. Adding a Primary Key to a Table That Already Has One

The most frequent cause is running ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY on a table where a primary key already exists — often because a deployment script is executed more than once without idempotency checks.

-- First execution succeeds
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);

-- Second execution triggers ORA-02260
ALTER TABLE employees
ADD CONSTRAINT pk_employees2 PRIMARY KEY (employee_id);
-- ERROR: ORA-02260: table can have only one primary key

-- Check existing primary key before acting
SELECT constraint_name, constraint_type, status
FROM   user_constraints
WHERE  table_name       = 'EMPLOYEES'
  AND  constraint_type  = 'P';

2. Duplicate Primary Key Definition in CREATE TABLE

Defining PRIMARY KEY at both the column level and the table level within the same CREATE TABLE statement will immediately raise ORA-02260.

-- ❌ Wrong: Primary key defined twice
CREATE TABLE orders (
    order_id    NUMBER PRIMARY KEY,        -- column-level PK
    order_date  DATE         NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY (order_id)  -- duplicate → ORA-02260
);

-- ✅ Correct: Table-level definition only (recommended for clarity)
CREATE TABLE orders (
    order_id    NUMBER       NOT NULL,
    order_date  DATE         NOT NULL,
    customer_id NUMBER       NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY (order_id)
);

-- ✅ Correct: Composite primary key
CREATE TABLE order_items (
    order_id   NUMBER NOT NULL,
    item_seq   NUMBER NOT NULL,
    product_id NUMBER NOT NULL,
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, item_seq)
);

3. Primary Key Conflict During Migration or Import

When using Oracle Data Pump (impdp) or custom migration scripts, importing a primary key definition into a table that already has one causes a conflict.

-- Step 1: Identify and drop the existing primary key before re-importing
SELECT constraint_name
FROM   user_constraints
WHERE  table_name      = 'TARGET_TABLE'
  AND  constraint_type = 'P';

ALTER TABLE target_table
DROP CONSTRAINT pk_target_table;

-- Step 2: Re-create after migration is complete
ALTER TABLE target_table
ADD CONSTRAINT pk_target_table PRIMARY KEY (id_column);

Quick Fix Solutions

Use this idempotent PL/SQL pattern to safely add a primary key without risking ORA-02260:

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;
/

To drop and recreate a primary key cleanly:

-- Drop existing PK (CASCADE removes dependent foreign keys)
ALTER TABLE employees
DROP CONSTRAINT pk_employees CASCADE;

-- Recreate with proper index tablespace
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id)
USING INDEX TABLESPACE users_idx;

Prevention Tips

1. Always write idempotent DDL scripts.
Every deployment script should check for the existence of a constraint before attempting to create it. Adopt the PL/SQL conditional pattern shown above as a team standard, especially in CI/CD pipelines where scripts may be executed repeatedly across environments.

2. Use ERD tools and enforce schema reviews.
Design primary keys explicitly in an ERD tool (e.g., Oracle SQL Developer Data Modeler, ERwin) before writing DDL. Conduct mandatory peer reviews of all schema change scripts, and periodically compare schema states between development, staging, and production environments using tools like DBMS_METADATA to catch drift early.

Related Oracle Errors

ORA-02261 – such unique or primary key already exists in the table: Raised when a duplicate unique key is defined, similar in nature to ORA-02260.
ORA-02270 – no matching unique or primary key for this column-list: Occurs when a foreign key references a table without a valid primary or unique key.
ORA-00001 – unique constraint violated: Triggered when duplicate data is inserted into a column governed by a primary key constraint.

📖 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