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

2 thoughts on “ORA-01758 when trying to alter table

  1. Prasad

    Yes, we can add a colum with NOT NULL with default value as explained to prevent ORA-01758. However, you can change to some other value later by update statement later.. but you can not remove the data completely as it’s NOT NULL column.. try it.. and you will get ‘ORA-01400: cannot insert NULL into ‘

Leave a Reply

Your email address will not be published. Required fields are marked *