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
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 ‘
That is true. But if we are adding a not null colum the all purpose of it is to be not null isn’t it?