"Merge" two rows in a Postgres table, with foreign keys

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 authors(id)* I thought about doing it like this (within a transaction): 1. Change Mark Twain id to 2, letting UPDATE CASCADE take care of changing the references. 2. Delete Mark Twain entry But this runs into a few problems, mainly: 1. The first step creates a duplicate primary key 2. I'm not sure how to reference the right row to delete, once they both have the same ID! 3. The DELETE CASCADE worries me for the second step There's also a subtler problem, that can be illustrated with a portion of my (poorly curated) books table: 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 books. Is there a way to do this, and work around most/all of these issues? Using Postgres 9.4.