Skip to Main Content
  • Questions
  • INITIALLY DEFFERED FOREIGN KEY constraint doesn't work

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: May 26, 2023 - 1:41 pm UTC

Last updated: May 31, 2023 - 1:44 pm UTC

Version: Oracle 18c XE

Viewed 1000+ times

You Asked

Hi,
I would like to create initally deferred constraint for FK as below. The problem is, that the constraint cars_service_car_id_fk is validated after DELETE statement, not at the end of transaction(COMMIT). I don't understand this behavior...

DROP TABLE cars;
CREATE TABLE cars(
  car_id          INTEGER
, name             VARCHAR2(20)
, CONSTRAINT cars_pk PRIMARY KEY(car_id) 
);

DROP TABLE cars_service;
CREATE TABLE cars_service(
  service_id INTEGER
, car_id          INTEGER
, CONSTRAINT cars_service_pk PRIMARY KEY(service_id) 
, CONSTRAINT cars_service_car_id_fk
  FOREIGN KEY (car_id)
  REFERENCES cars(car_id) <b>DEFERRABLE INITIALLY DEFERRED</b>
);

INSERT INTO cars(car_id, name)
VALUES(1, 'Volvo');

INSERT INTO cars_service(service_id, car_id)
VALUES(1, 1);

COMMIT;


DELETE FROM cars
WHERE car_id = 1;

-- And now Iim getting error to early..
<b>
DELETE FROM cars
WHERE car_id = 1
Error report -
ORA-02292:</b>


and Chris said...

Sorry, I'm not able to reproduce this...

DROP TABLE cars_service;
DROP TABLE cars;
CREATE TABLE cars(
  car_id          INTEGER
, name             VARCHAR2(20)
, CONSTRAINT cars_pk PRIMARY KEY(car_id) 
);

CREATE TABLE cars_service(
  service_id INTEGER
, car_id          INTEGER
, CONSTRAINT cars_service_pk PRIMARY KEY(service_id) 
, CONSTRAINT cars_service_car_id_fk
  FOREIGN KEY (car_id)
  REFERENCES cars(car_id) DEFERRABLE INITIALLY DEFERRED
);

INSERT INTO cars(car_id, name)
VALUES(1, 'Volvo');

INSERT INTO cars_service(service_id, car_id)
VALUES(1, 1);

COMMIT;

DELETE FROM cars
WHERE car_id = 1;

SELECT COUNT(*) FROM cars;
/*
  COUNT(*)
----------
         0
*/
COMMIT;
/*
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (CHRIS.CARS_SERVICE_CAR_ID_FK) violated - child record found
*/


I've tried on various versions from 12.2 - 23c.

Double-check that the FK is indeed deferred and that constraints are running in their default mode for in your session:

SELECT deferrable, deferred FROM user_constraints
WHERE  constraint_name = 'CARS_SERVICE_CAR_ID_FK';

ALTER SESSION SET CONSTRAINTS = DEFAULT;


If you're still hitting this problem, please run the example on Live SQL, save the script, and post a link to it here so we can see what the issue is.

Rating

  (1 rating)

Comments

Problem solved

A reader, May 30, 2023 - 2:10 pm UTC

It has worked fine now. Apparently I missmatched something. Thank you for your help.
Chris Saxon
May 31, 2023 - 1:44 pm UTC

Thanks for confirming

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.