Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


create table person1( id integer generated always as identity 
                   , name varchar2(50)
                   , active varchar2(1) default 'Y' not null
                   );
insert into person1(name)
    select 'monica'  from dual union all
    select null      from dual union all
    select null      from dual union all
    select 'stephan' from dual union all
    select null      from dual;
5 rows affected

select * from person1;


ID
NAME
ACTIVE


1
monica
Y

2
null
Y

3
null
Y

4
stephan
Y

5
null
Y


-- 1. The correct way.  
-- update existing data
update person1
   set name = 'PERSON' || to_char(rownum,'FM999') 
 where name is null;
3 rows affected

select * from person1;


ID
NAME
ACTIVE


1
monica
Y

2
PERSON1
Y

3
PERSON2
Y

4
stephan
Y

5
PERSON3
Y


-- fix the system
alter table person1 modify name not null;  
insert into person1(active) values ('N');
ORA-01400: cannot insert NULL into ("FIDDLE_BZWQTSVXDIHTVOHPZKBV"."PERSON1"."NAME")

-- 2. Making a bandaid
create table person2( id integer generated always as identity 
                   , name varchar2(50)
                   , active varchar2(1) default 'Y' not null
                   );
insert into person2(name)
    select 'monica'  from dual union all
    select null      from dual union all
    select null      from dual union all
    select 'stephan' from dual union all
    select null      from dual;
5 rows affected

select * from person2;


ID
NAME
ACTIVE


1
monica
Y

2
null
Y

3
null
Y

4
stephan
Y

5
null
Y


-- prepare for long term bandaid 
create sequence unknown_person_seq;
-- update existing data
update person2
   set name = 'PERSON' || to_char(unknown_person_seq.nextval, 'FM99')
 where name is null;
3 rows affected

select * from person2;


ID
NAME
ACTIVE


1
monica
Y

2
PERSON1
Y

3
PERSON2
Y

4
stephan
Y

5
PERSON3
Y


-- adjust the system
alter table person2 modify name default 'PERSON' || to_char(unknown_person_seq.nextval, 'FM99');
-- then
insert into person2(active) values ('N');
1 rows affected

select * from person2;


ID
NAME
ACTIVE


1
monica
Y

2
PERSON1
Y

3
PERSON2
Y

4
stephan
Y

5
PERSON3
Y

6
PERSON4
N