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

Command

Alter Table Tblpledgeemaster add nFundingallowed number(16,4) not null /* Contains Total Funding allowed */ ;

 

Error

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

 

 

Solution

 

1) provide a default value for the column along with alter table add column command.

 

 

2) Add the column without the NOT NULL constraint then update then modify not null

 

 

Alter Table Tblpledgeemaster add nFundingallowed number(16,4) /* Contains Total Funding allowed */ ;

 

UPDATE Tblpledgeemaster set nFundingallowed= 0;  /* Please contact to business team or developer for default value */

 

commit;

 

ALTER TABLE Tblpledgeemaster MODIFY (nFundingallowed NOT NULL);

 

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