Oracle ORA-02250 Error: Causes and Solutions Complete Guide

ORA-02250: Missing or Invalid Constraint Name

ORA-02250 is an Oracle error that occurs when a constraint name is either missing or invalid during a CREATE TABLE or ALTER TABLE statement. Oracle requires that every explicitly named constraint follows standard identifier rules — the name must begin with a letter, cannot contain most special characters, and cannot be an Oracle reserved word. This error is common during migrations from other databases (e.g., MySQL, PostgreSQL) where naming conventions differ significantly.

---

Top 3 Causes

1. Using Reserved Words or Invalid Characters as Constraint Names

Oracle identifiers cannot use reserved words or start with numbers. Using names like SELECT or 1ST_PK will immediately trigger ORA-02250.

-- BAD: Reserved word used as constraint name
ALTER TABLE employees
ADD CONSTRAINT SELECT_PK PRIMARY KEY (employee_id);
-- ORA-02250 thrown

-- BAD: Name starts with a number
CREATE TABLE departments (
    dept_id   NUMBER,
    dept_name VARCHAR2(100),
    CONSTRAINT 1ST_PK PRIMARY KEY (dept_id)
);
-- ORA-02250 thrown

-- GOOD: Valid constraint names
ALTER TABLE employees
ADD CONSTRAINT PK_EMPLOYEES PRIMARY KEY (employee_id);

CREATE TABLE departments (
    dept_id   NUMBER,
    dept_name VARCHAR2(100),
    CONSTRAINT PK_DEPARTMENTS PRIMARY KEY (dept_id)
);

2. Using the CONSTRAINT Keyword Without a Name

When you write the CONSTRAINT keyword, Oracle expects a name immediately after it. Jumping straight to PRIMARY KEY or UNIQUE without a name causes ORA-02250.

-- BAD: No name after CONSTRAINT keyword
CREATE TABLE orders (
    order_id    NUMBER,
    customer_id NUMBER,
    CONSTRAINT PRIMARY KEY (order_id)
);
-- ORA-02250 thrown

-- GOOD: Provide a name
CREATE TABLE orders (
    order_id    NUMBER,
    customer_id NUMBER,
    CONSTRAINT PK_ORDERS PRIMARY KEY (order_id)
);

-- GOOD: Omit CONSTRAINT keyword entirely (Oracle auto-generates a name)
CREATE TABLE orders (
    order_id    NUMBER,
    customer_id NUMBER,
    PRIMARY KEY (order_id)
);

3. Wrong Quoting — Using Single Quotes Instead of Double Quotes

Oracle uses double quotes (") for quoted identifiers. Using single quotes (') around a constraint name is a syntax error.

-- BAD: Single quotes around constraint name
ALTER TABLE employees
ADD CONSTRAINT 'UQ_EMP_EMAIL' UNIQUE (email);
-- ORA-02250 thrown

-- GOOD: Double quotes (if spaces or special chars are needed)
ALTER TABLE employees
ADD CONSTRAINT "UQ EMP EMAIL" UNIQUE (email);

-- BEST: Avoid spaces entirely — use underscores
ALTER TABLE employees
ADD CONSTRAINT UQ_EMP_EMAIL UNIQUE (email);

---

Quick Fix Solutions

1. Rename the constraint to follow Oracle identifier rules: start with a letter, use only letters, numbers, _, $, #.
2. Remove the CONSTRAINT keyword if you don't need a custom name — Oracle will auto-assign a system name.
3. Check for reserved words before naming constraints. Query V$RESERVED_WORDS to verify.

-- Verify reserved words
SELECT keyword FROM v$reserved_words
WHERE keyword = 'YOUR_PROPOSED_NAME';

-- Check existing constraints on a table
SELECT constraint_name, constraint_type, status
FROM   user_constraints
WHERE  table_name = 'YOUR_TABLE_NAME';

---

Prevention Tips

Adopt a Naming Convention: Standardize constraint names across your team using a clear prefix pattern. This avoids reserved word conflicts and duplicate names.

-- Recommended naming convention
-- PK_<TABLE>         → Primary Key
-- FK_<CHILD>_<PARENT>→ Foreign Key
-- UQ_<TABLE>_<COL>   → Unique
-- CK_<TABLE>_<RULE>  → Check

CREATE TABLE employees (
    employee_id NUMBER        NOT NULL,
    email       VARCHAR2(200) NOT NULL,
    dept_id     NUMBER,
    salary      NUMBER,
    CONSTRAINT PK_EMPLOYEES   PRIMARY KEY (employee_id),
    CONSTRAINT UQ_EMP_EMAIL   UNIQUE (email),
    CONSTRAINT FK_EMP_DEPT    FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
    CONSTRAINT CK_EMP_SALARY  CHECK (salary > 0)
);

Always Validate DDL in a Dev/Test Environment First: Before applying any DDL to production, run scripts in a lower environment to catch naming errors early. Use USER_CONSTRAINTS to audit existing names and avoid duplicates.

---

Related Errors

• ORA-02264 — Constraint name already in use by another constraint.
• ORA-00904 — Invalid identifier, often appearing alongside naming mistakes.
• ORA-02231 — Missing or invalid option in ALTER TABLE statements.

---

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