Altering table property to default when NULL only for new inserts

Question

Is there any way in Oracle that we can set Default column value when NULL only for New inserts? I don't want to change the Existing records if they have NULL.

I want to do this at table level. Not with NVL insert logic.

Your question is actually very similar to [this answer](https://stackoverflow.com/questions/21057275/alter-table-to-modify-default-value-of-column), but I gave an answer below as well. — Tim Biegeleisen, Nov 30 '18 at 05:24

score 2 · Answer 1 · answered Nov 30 '18 at 05:23

As far as I know, if you alter the table and set a default value for a column, it should only affect new records which would come in via an insert, not existing records.

ALTER TABLE yourTable MODIFY (col VARCHAR(100) DEFAULT 'some value');

Using the above approach, col values which are already NULL should remain NULL, at least from the point of view of inserts not changing those NULL values. And newly inserted records which do not specify a value for col should receive the default value some value.

I tried this. The old values didn't change. But when I insert new records with NULL. It gets inserted as NULL. It doesn't get the default value. — Harikrishnan Balachandran, Nov 30 '18 at 08:34

score 0 · Answer 2 · answered Nov 30 '18 at 20:32

Here's a demonstration which shows what's going on.

First, a test table and some inserts:

SQL> create table test (id number, col varchar2(10));

Table created.

SQL> insert into test (id, col) values (1, null);

1 row created.

SQL> insert into test (id, col) values (2, 'Littlefoot');

1 row created.

SQL> select * from test;

        ID COL
---------- ----------
         1
         2 Littlefoot

Alter the table so that newly added rows contain 'some value' for the COL column:

SQL> alter table test modify col default 'some value';

Table altered.

OK; and now, important part of the story: pay attention to following:

SQL> -- this won't work as you wanted, because I explicitly inserted NULL into COL
SQL> insert into test (id, col) values (3, null);

1 row created.

SQL> -- this will work, because COL is omitted from the INSERT statement
SQL> insert into test (id) values (4);

1 row created.

SQL> select * From test;

        ID COL
---------- ----------
         1
         2 Littlefoot
         3
         4 some value

SQL>

See? If you explicitly put NULL into a column, it won't get the default value.

However, if you were on 12c (I know, you aren't - just saying, for future reference), there's yet another option: DEFAULT ON NULL. It goes like this:

SQL> alter table test modify col default on null 'some value';
alter table test modify col default on null 'some value'
*
ERROR at line 1:
ORA-02296: cannot enable (SCOTT.) - null values found

Ooops! Won't work if there are NULLs in the column. I know #2 that you don't want to modify existing rows, but - for this demonstration, I'll do that:

SQL> update test set col = 'x' where col is null;

2 rows updated.

SQL> alter table test modify col default on null 'some value';

Table altered.

OK; let's see how it behaves: I'm explicitly inserting NULL into the column. In the previous example, it didn't put 'some value' in there, but left it NULL. How about now?

SQL> insert into test (id, col) values (5, null);

1 row created.

SQL> select * From test;

        ID COL
---------- ----------
         1 x
         2 Littlefoot
         3 x
         4 some value
         5 some value

Nice; we have 'some value' in the column.

Now you have some more info about the issue; see if it helps.

score 0 · Answer 3 · answered Dec 01 '18 at 06:32

As Littlefoot mentioned, If you explicitly put NULL into a column, it won't get the default value.

If no value is mentioned for the column in the insert query, it uses DEFAULT. But, an explicit NULL overrides the default expression.

For 12c and above you can use the DEFAULT ON NULL option. For prior versions, only way as far as I can tell is to replicate that functionality through a TRIGGER

CREATE TABLE YOURTABLE (  yourcolumn VARCHAR(100) );


CREATE OR REPLACE TRIGGER trg_mod_yourtabcol BEFORE
     INSERT ON yourtable
     FOR EACH ROW
     WHEN ( new.yourcolumn IS NULL )

BEGIN
     :new.yourcolumn := 'SOME DEFAULT VALUE';
END;
/


INSERT INTO YOURTABLE(yourcolumn) VALUES(NULL);

select * from YOURTABLE;

Table YOURTABLE created.


Trigger TRG_MOD_YOURTABCOL compiled


1 row inserted.


YOURCOLUMN                                                                                          
----------------------------------------------------------------------------------------------------
SOME DEFAULT VALUE

1 row selected.

Altering table property to default when NULL only for new inserts

3 Answers3