0
0
Hi everyone!
I recently encounter an issue where I was trying to add some new columns with NOT NULL constraint in one of the existing tables in the database and when I executed the query, the Oracle database showed me one of its error codes – ORA-01758 along with a message – “Table must be empty to add the mandatory (not null) column.”
I came to know that if a table is already populated and you are trying to insert a new column with NOT NULL constraint in it then this error will come. I found the solution and now sharing it with all of you so that you can easily handle this.
1. The simplest way is to DROP the table and create a new one along with the column having NOT NULL constraints. This will solve the issue but wait, what if we have data in the table? In this case, follow step 2.
2. The second solution is to add the column without adding NOT NULL constraints and once the column gets added then modify it with not null constraints. For example:
ALTER TABLE <TABLE_NAME> ADD COLUMN (<COLUMN_NAME> <DATA_TYPE(SIZE)>)Let’s say that we have an EMPLOYEE table and we need to add a joining date (JOINING_DT) column with not null constraints then we will write the query as:
ALTER TABLE USER1.EMPLOYEE ADD COLUMN (JOINING_DT DATE);Here, USER1 is the schema name. Once you have executed the alter command, use the below-mentioned query to modify the column.
ALTER TABLE USER1.EMPLOYEE MODIFY (JOINING_DT NOT NULL);By doing this, you can resolve the ORA-01758 error. Hope this post will be helpful.
Follow me on Twitter, and Instagram, and connect with me on LinkedIn.
For consulting, mail me at techanswersweb@gmail.com.
