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

While adding NOT NULL column into a table, the below error occurred:

SQL> Alter table CM_ODS_DWN_STG add MATCH_EVT_ID  CHAR(12) NOT NULL;
Alter table CM_ODS_DWN_STG add MATCH_EVT_ID  CHAR(12) NOT NULL
            *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

The workarounds could be :

1) Default value is given:

SQL> Alter table CM_ODS_DWN_STG add MATCH_EVT_ID  CHAR(12) DEFAULT 'EMPTY' NOT NULL;

2) Remove NOT NULL:

SQL> Alter table CM_ODS_DWN_STG add MATCH_EVT_ID  CHAR(12);

3) Backup the table to another staging table, truncate the table and then add the NOT NULL column, insert the records back to the table from the staging table.

Advertisements
This entry was posted in ORA- ERRORS and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s