Tag Archives: alte table

ORA-01758 when trying to alter table

If you are trying to add a new Not NULL columns to a table and you get the following error:

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

Since the values in the new column are null (after all, you just added the column) the new constraint can not be satisfied.

There are several option to overcome ORA-01758:

1) provide a default value for the column.

ALTER TABLE table-name ADD column-name VARCHAR2(15) DEFAULT ‘X’ NOT NULL

If you don’t want the default value you can remove it after the column will be populated with the new data.

2) Add the column without the NOT NULL constraint,

ALTER TABLE table-name ADD (column-name VARCHAR2(15));

— add the data

UPDATE table-name set column-name=’x’;

— apply the NOT NULL  constraint.

ALTER TABLE table-name MODIFY (column-name NOT NULL)

3) Empty the table, apply the NOT NULL and add the data back to the table