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>
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.