umzzil nng

Posted on Jul 31 • Originally published at oraerror.com

Oracle ORA-01758 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01758: table must be empty to add mandatory (NOT NULL) column

ORA-01758 is an Oracle error that occurs when you attempt to add a NOT NULL column to a table that already contains data. Since existing rows would have NULL values in the new column — which violates the NOT NULL constraint — Oracle blocks the operation entirely. The fix is straightforward: either the table must be empty, or you must provide a DEFAULT value alongside the NOT NULL constraint.

Top 3 Causes

1. Adding a NOT NULL Column Without a DEFAULT Value

The most common cause. When you issue an ALTER TABLE ... ADD statement with NOT NULL but no DEFAULT, Oracle cannot populate existing rows with a valid value.

-- This will fail with ORA-01758 if the table has data
ALTER TABLE employees
ADD (status VARCHAR2(10) NOT NULL);

-- ORA-01758: table must be empty to add mandatory (NOT NULL) column

2. Migrating DDL Scripts from Dev to Production

Development databases are often empty or lightly populated, so the same script works fine in dev but fails in production where millions of rows exist.

-- Works fine in dev (empty table), fails in prod (millions of rows)
ALTER TABLE orders
ADD (region_code CHAR(3) NOT NULL);

3. ORM Auto-Migration in Production

Frameworks like Hibernate or Django ORM can auto-generate ALTER TABLE statements when entity classes change. These generated scripts rarely include DEFAULT values, causing ORA-01758 in production.

-- Auto-generated DDL from ORM (dangerous in production)
ALTER TABLE customers
ADD (loyalty_tier VARCHAR2(20) NOT NULL);
-- Fails immediately if any rows exist

Quick Fix Solutions

Fix 1: Add DEFAULT + NOT NULL Together (Recommended — Oracle 11g+)

Oracle 11g and above handles this efficiently without physically updating every row.

-- Safe and performant on Oracle 11g+
ALTER TABLE employees
ADD (status VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL);

Fix 2: Three-Step Approach (Compatible with All Versions)

-- Step 1: Add column as nullable
ALTER TABLE employees
ADD (region_code CHAR(3));

-- Step 2: Populate existing rows
UPDATE employees
SET region_code = 'US'
WHERE region_code IS NULL;
COMMIT;

-- Step 3: Apply NOT NULL constraint
ALTER TABLE employees
MODIFY (region_code CHAR(3) NOT NULL);

Fix 3: Verify Data Before Running DDL

-- Always check row count before adding NOT NULL columns
SELECT COUNT(*) FROM employees;

-- If result is 0, you can safely add NOT NULL without DEFAULT
-- If result > 0, use Fix 1 or Fix 2 above

Prevention Tips

Always specify DEFAULT with NOT NULL in ALTER TABLE statements. Make this a mandatory coding standard across your team. This single habit eliminates ORA-01758 entirely in most scenarios.

-- Bad practice
ALTER TABLE invoices ADD (paid_flag CHAR(1) NOT NULL);

-- Good practice
ALTER TABLE invoices ADD (paid_flag CHAR(1) DEFAULT 'N' NOT NULL);

Use a database migration tool such as Flyway or Liquibase to version-control all DDL changes. These tools enforce a review process before scripts reach production, and team members can catch missing DEFAULT values during code review — long before the script ever touches a live database.

Related Errors

ORA-02296 — Triggered when enabling a NOT NULL constraint on a column that already contains NULL values. Closely related to ORA-01758 and often encountered when the three-step fix is applied incorrectly.
ORA-01407 — Raised when attempting to UPDATE a NOT NULL column with a NULL value after the constraint is in place.
ORA-01735 — Invalid ALTER TABLE option; can appear alongside DDL syntax mistakes related to constraint definitions.

📖 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