As requested here is a full script and the issue does not arise.
My sincere apologies it appears the issue is most likely with my client programming code.
My first ever post here. I shall definitely be providing a full working SQL script from the very start on any future post.
Many thanks and apologies again.
CREATE TABLE public.itineraryx (
dk varchar(10) NULL,
"locator" varchar(20) NOT NULL,
CONSTRAINT itinerary_pkeyx PRIMARY KEY (locator)
);
CREATE TABLE public.hotel_bookingx (
itinerary varchar(20) NULL,
"hotelName" varchar(50) NULL,
id bigserial NOT NULL,
CONSTRAINT hotel_booking_pkeyx PRIMARY KEY (id)
);
ALTER TABLE public.hotel_bookingx ADD CONSTRAINT hotel_booking_itinerary_foreignx FOREIGN KEY (itinerary) REFERENCES itineraryx(locator) ON DELETE CASCADE;
INSERT INTO public.itineraryx
(dk, "locator")
VALUES('tba', 'TEST0001');
INSERT INTO public.hotel_bookingx
(itinerary, "hotelName")
VALUES('TEST0001', 'Crown');
BEGIN TRANSACTION;
delete from itineraryx i where i."locator" = 'TEST0001';
INSERT INTO public.itineraryx
(dk, "locator")
VALUES('tba2', 'TEST0001');
INSERT INTO public.hotel_bookingx
(itinerary, "hotelName")
VALUES('TEST0001', 'Crown2');
COMMIT TRANSACTION;
Really appreciate the input thank you.
I shall try to provide a full script, I guess if the cascades are relevant in going to have to also provide all of the indirect table creations and all of the earlier inserts to those various tables.
At the very least I'll try to provide a fuller error message and a minimum structure that will cause it to occur.
Andrew
Do I need some particular kind of settings on my transaction to be able to delete and insert afresh in the same transaction?
No. I cannot reproduce your claim with a trivial example on stock 13.5.
You will need to be less vague and at least provide an exact reproducer script.
In case it is relevant - the first delete will lead to cascaded deletes on children.
This may indeed be relevant. Again, you need to provide an exact reproducer, not expect others to reverse-engineer one for you.
David J.