Overview

When adding a column with a NOT NULL constraint to a table that already contains data using the ALTER TABLE ADD method,
an error occurs if the existing data contains NULL values.

This document explains two methods to avoid this error and add a column with a NOT NULL constraint.

Method

1. Add a NOT NULL Column with a DEFAULT Value

Test Scenario

CREATE TABLE TEST (A NUMBER, B NUMBER);
INSERT INTO TEST VALUES (10, '');
COMMIT;

• When directly adding a NOT NULL column

ALTER TABLE TEST ADD (C VARCHAR2(10) NOT NULL);
TBR-7045: A column contains a NULL value: cannot create or enable the constraint.

• Adding a column with a DEFAULT value

ALTER TABLE TEST ADD (C VARCHAR2(10) DEFAULT 'TEST' NOT NULL);
Table 'TEST' altered.

2. Add the Column First, Then Apply the NOT NULL Constraint

(Using CHECK Constraint + NOVALIDATE)

Test Scenario

CREATE TABLE TEST (A NUMBER, B NUMBER);
INSERT INTO TEST VALUES (10, '');
COMMIT;
ALTER TABLE TEST ADD (D VARCHAR2(10));

• When applying the CHECK constraint

ALTER TABLE TEST ADD CONSTRAINT D_NOT_NULL CHECK (D IS NOT NULL);
TBR-7161: Unable to validate (TEST.D_NOT_NULL): constraint violated.

• Adding the constraint using the NOVALIDATE option

ALTER TABLE TEST ADD CONSTRAINT D_NOT_NULL CHECK (D IS NOT NULL) NOVALIDATE;
Table 'TEST' altered.

※ The NOVALIDATE option does not validate the constraint against existing data,
and applies the constraint only to data entered thereafter.