CODEWITHSUNDEEP
sqloracle-database
Wei Kang
Wei Kang

Reputation: 23

Null values found

I want to insert a not null value. This is a example:

create table time(
a varchar2(9),
b varchar2(9),
c varchar2(9));

table create

insert into time (a,c) values ('qq','ee');

table altered

When I key this:

alter table time
modify b varchar2(9) not null;

This error come out:

alter table time
*
ERROR at line 1: 
ORA-02296: cannot enable (DIP0114713.) - null values found

So how I insert a value to a and c column and also column b is not null?

Upvotes: 1

Views: 1157

Answers (3)

Boneist
Boneist

Reputation: 23588

It is possible to create a constraint without it impacting existing rows:

create table test1(
a varchar2(9),
b varchar2(9),
c varchar2(9));

insert into test1 (a,c) values ('qq','ee');

commit;

alter table test1 modify b varchar2(9) not null novalidate;

select * from test1;

A         B         C        
--------- --------- ---------
qq                  ee       


insert into test1 (a,c) values ('qq2','ee2');

ORA-01400: cannot insert NULL into ("SCHEMA1"."TEST1"."B")

However, I would NOT recommend using this, as if you need to create future constraints on the table, it could massively complicate things, since you have "invalid" data in the table.

Far better to take the hit and "fix" the data at the point when you're creating a constraint, rather than shrug your shoulders and leave it for another time - it will come back to bite you (or if not you, some other poor soul who's stuck maintaining the database in the future!) on the bum.

Upvotes: 0

siva
siva

Reputation: 1

It's not possible. First you need to update the data in column b and then apply not null constraint.

Update time set b= 'NA' where b is null
go
ALTER TABLE time
ALTER COLUMN b varchar(9) NOT NULL
go

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270793

If you don't mention a column in an insert, then it gets the default value. By default, the default value is NULL. You can specify something else:

create table time (
    a varchar2(9),
    b varchar2(9) not null default 'NO VALUE',
    c varchar2(9))
);

EDIT:

To get your alter table to work, update the value first:

update time
    set b = 'NO VALUE'
    where b is null;

Upvotes: 5

Related Questions