What needs to be taken care of if I want to add a new field with a NOT NULL to an existing table that has so many, or n number of records? What will be the impact? Can I do this?
Let's look at the following example:
SQL> desc emp Name Null? Type ----------------------------------------- -------- ---------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> alter table emp add nulltest varchar2(5) not null ; alter table emp add nulltest varchar2(5) not null * ERROR at line 1: ORA-01758: table must be empty to add mandatory (NOT NULL) column
There's your answer.... if a table already has data you cannot add a new field with a NOT NULL constraint.
If what you're trying to accomplish is to make sure that you never have null values in a column, then try something like this:
SQL> alter table emp add (nulltest varchar2(5) default 'XXXXX') ;
The default clause will force the column to have a value if no value is given to the column when it is inserted. This keeps out the unwanted nulls and also has the benefit of making index usage work more optimally.
Another way to accomplish creating a column with NOT NULL would be to create a duplicate table using CREATE TABLE AS syntax. Then truncate the original table, add the new column with the NOT NULL constraint and then use INSERT INTO...SELECT to put your data back in. You could also do this with exp/imp utilities as well.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.