Greg Mullane has an excellent post on updating unique columns. A simple problem, but one that can be troublesome in practice:
[T]here is one circumstance when [unique constraints] can be a real pain: swapping the values of the rows. In other words, a transaction in which the column values start unique, and end unique, but may not be so in the middle.Read his article -- I wouldn't have thought of his "reversing the polarity" method. Clever! But my first thought when I read this was, "Aha, Greg missed one." Surely the easiest way is to simply create a deferrable constraint (where you can elect to have the constraint only checked at the end of the transaction, instead of at the end of each statement)! So I gave it a try:
=> CREATE TABLE foo (
i int
CONSTRAINT foo_pk PRIMARY KEY DEFERRABLE
);
ERROR: misplaced DEFERRABLE clause
At first I thought this indicated a syntax error, but my syntax was correct. After some googling, it turns out that this is the rather unhelpful message that means, as the docs explain,
Only foreign key constraints currently accept this clause. All other constraint types are not deferrable.Huh. Sure enough, all the places I'd used DEFERRABLE in the past were with FK constraints, so I never noticed this limitation. (Fixing this is on the TODO list for future action.)
Comments