Update or Delete Violates foreign key constraint

I have two tables that look like the following.

CREATE TABLE book (
    book_id BIGSERIAL PRIMARY KEY,
    book_special_id character(10) NOT NULL DEFAULT random_string(10) UNIQUE,
    author_id bigint REFERENCES author(author_id) ON DELETE RESTRICT,
    category_id bigint REFERENCES category(category_id) ON DELETE RESTRICT,
    title text NOT NULL,
    subtitle text,
    book_text text
);

CREATE TABLE booksubcategory (
    booksubcategory_id BIGSERIAL PRIMARY KEY,
    book_id BIGSERIAL REFERENCES book(book_id) ON DELETE CASCADE,
    subcategory_id BIGSERIAL REFERENCES subcategory(subcategory_id) ON DELETE RESTRICT,
    CONSTRAINT booksubcategory_book_id_subcategory_id_key UNIQUE (book_id, subcategory_id)
);

​x
 
CREATE TABLE book (    book_id BIGSERIAL PRIMARY KEY,    book_special_id character(10) NOT NULL DEFAULT random_string(10) UNIQUE,    author_id bigint REFERENCES author(author_id) ON DELETE RESTRICT,    category_id bigint REFERENCES category(category_id) ON DELETE RESTRICT,    title text NOT NULL,    subtitle text,    book_text text);​CREATE TABLE booksubcategory (    booksubcategory_id BIGSERIAL PRIMARY KEY,    book_id BIGSERIAL REFERENCES book(book_id) ON DELETE CASCADE,    subcategory_id BIGSERIAL REFERENCES subcategory(subcategory_id) ON DELETE RESTRICT,    CONSTRAINT booksubcategory_book_id_subcategory_id_key UNIQUE (book_id, subcategory_id));​

In this example the book_id column is the primary key in the book table, and the referenced foreign key in the booksubcategory table. When I try and run the following sql, I receive the error:

ERROR: update or delete on table "book" violates foreign key constraint "booksubcategory_book_id_fkey" on table "booksubcategory"
Detail: Key (book_id)=(888392) is still referenced from table "booksubcategory"

 
ERROR: update or delete on table "book" violates foreign key constraint "booksubcategory_book_id_fkey" on table "booksubcategory"Detail: Key (book_id)=(888392) is still referenced from table "booksubcategory"​

Here is what the SQL looks like.

INSERT INTO book (book_special_id, author_id, category_id, title, subtitle, book_text) 
VALUES ("D4jOko2IP0",34, 4, "Book Example", "Book Subtitle", "Some lengthy text") 
ON CONFLICT (book_special_id) 
DO UPDATE SET author_id=EXCLUDED.author_id, book_id=EXCLUDED.book_id,  category_id=EXCLUDED.category_id, title=EXCLUDED.title, subtitle=EXCLUDED.subtitle, book_text=EXCLUDED.book_text;

 
INSERT INTO book (book_special_id, author_id, category_id, title, subtitle, book_text) VALUES ("D4jOko2IP0",34, 4, "Book Example", "Book Subtitle", "Some lengthy text") ON CONFLICT (book_special_id) DO UPDATE SET author_id=EXCLUDED.author_id, book_id=EXCLUDED.book_id,  category_id=EXCLUDED.category_id, title=EXCLUDED.title, subtitle=EXCLUDED.subtitle, book_text=EXCLUDED.book_text;​

In this situation the sql should update the columns because the book_special_key already exists in the book table.

I’m familiar with the reason why updates and deletes on foreign key constraints can fail for integrity reasons, but in my case I’m not updating the book_id directly, just the columns in the book table. Also I have ON DELETE CASCADE set on the foreign key in the child table. Can someone tell my why I’m experiencing this issue?

Answer

The inserted row clashes on unique key special_book_id, then the conflict rule tries to update the duplicate row.

But what is the value book_id of a NEW row that was not yet inserted due to conflicts and is autogen? Well, either null or a new serial.

So, whatever the case, you are updating book_id to null or a new serial and it fails as the old book_id value, that is disappearing, has references.

Remove the update to column book_id and it should work.

Advertisement

Answer