I am keeping a database of books I've read, using the following two tables in PostgreSQL:
CREATE TABLE authors ( id SERIAL PRIMARY KEY, name text ); CREATE TABLE books ( id SERIAL PRIMARY KEY, title text, author_id integer REFERENCES authors(id) ON UPDATE CASCADE ON DELETE CASCADE, UNIQUE(title, author_id) );
Now when going through my list of authors, I found the following two entries:
id | name ---------- 1 | Mark Twain 2 | Samuel Clemens
What I'd like to do is delete the "Mark Twain" entry, and effectively update all books referencing "Mark Twain" to reference "Samuel Clemens". I know I could do this manually, but I want a solution that works, regardless of which tables are referencing the
I thought about doing it like this (within a transaction):
- Change Mark Twain
idto 2, letting
UPDATE CASCADEtake care of changing the references.
- Delete Mark Twain entry
But this runs into a few problems, mainly:
- The first step creates a duplicate primary key
- I'm not sure how to reference the right row to delete, once they both have the same ID!
DELETE CASCADEworries me for the second step
There's also a subtler problem, that can be illustrated with a portion of my (poorly curated)
id | title | author_id ------------------------------------ 1 | "Huckleberry Finn" | 1 2 | "Huckleberry Finn" | 2
Here, even if my two-step process succeeded, I would be violating the
UNIQUE contstraint on
Is there a way to do this, and work around most/all of these issues? Using Postgres 9.4.