High Performance PostgreSQL for Rails: `CHECK` constraint can't stand in for `UNIQUE` constraint (Pages 76–77)

Pragmatic Bookshelf PragProg Customers
,
3

Thanks again Douglass. You’re correct here. This was a mistake in the book. We’re in the process of correcting this now. The correction will primarily involve removing UNIQUE constraints from this section as it was incorrect to involve them in a discussion about check constraints as a transitional mechanism.

For unique constraint enforcement with lighter weight locking, I recommend using the unique index tactic. This adds the unique index concurrently, which avoids the heavy lock. Once added, the unique index is then used as the unique constraint definition.

I don’t think this was covered well in the book, so I’m going to include an example here for the Rideshare database.

Step #1: Add the unique index:

CREATE UNIQUE INDEX CONCURRENTLY
idx_vehicles_name_unique ON vehicles (name);

Step #2: Once that’s added (make sure it was added correctly, and is not in an INVALID state), then a unique constraint can be added to the column, using the unique index, with the USING INDEX keywords.

ALTER TABLE vehicles
ADD CONSTRAINT idx_vehicles_name_unique UNIQUE
USING INDEX idx_vehicles_name_unique;

Let me know what you think. Thanks again for noticing that and spending the time to write up this issue!

1 Like