CODEWITHSUNDEEP
javaspringoracle-databasehibernate
java123999
java123999

Reputation: 7394

Deletions with Spring data/Hibernate: ORA-01407: Cannot update to Null

I am trying to delete rows in a certain table from my database using the deleteAll() method in the CRUD Repository

When I do so I get this error relating to my DOG table, row DOG_OWNER:

ORA-01407: Cannot update DOG_OWNER to Null

Is the fix just to remove the not-null constraint on this row, or is there another workaround?

Upvotes: 1

Views: 4056

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21073

Below a simplified scenario with a FOREIGN KEYnot NULLable and with a constraint using ON DELETE SET NULL, which leads to the reported error after delete of the referenced key.

In this case indeed helps to lift the NOT NULLconstraint on the foreign key.

create table dog_owner 
(id number);
alter table dog_owner add  primary key (id);
insert into dog_owner values (1);

create table dog 
(id number,
dog_owner_id number not null); -- foreign key is not nullable ..
alter table dog add  primary key (id);
alter table dog add  foreign key (dog_owner_id) references dog_owner(id) 
ON DELETE SET NULL; -- but the contrains sets is to null...

insert into dog values (1,1);
commit;

delete from dog_owner where id = 1;

-- SQL-Fehler: ORA-01407: cannot update ("SCHEMA_NAME"."DOG"."DOG_OWNER_ID") to NULL 

alter table dog modify (dog_owner_id number  null);

delete from dog_owner where id = 1;

-- 1 rows deleted

Upvotes: 3

Related Questions