douglasshuang

douglasshuang

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

@andatki

The book states:

How can we safely add UNIQUE and NOT NULL constraints to tables, though, and make sure that all rows are eventually checked for compatibility with the constraint?

To do that, we can again use check constraints in a supportive, temporary role for UNIQUE and NOT NULL.

Even when the intended final constraint type is UNIQUE or NOT NULL, check constraints can stand in temporarily, to validate changes against new rows in preparation for adding the other constraint types on a permanent basis.

I understand and agree with the statements with regard to NOT NULL constraints, but I don’t think it’s possible to enforce uniqueness with a CHECK constraint. The PostgreSQL Glossary states:

Check constraint

A type of constraint defined on a relation which restricts the values allowed in one or more attributes. The check constraint can make reference to any attribute of the same row in the relation, but cannot reference other rows of the same relation or other relations.

If a CHECK constraint can’t reference other rows, then it can’t ensure that the current row doesn’t contain a value or values that are in another row.

Marked As Solved

andatki

andatki

Author of High Performance PostgreSQL for Rails

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!

Also Liked

douglasshuang

douglasshuang

Hi @andatki,

Thank you for all of your responses! Your planned changes look good.

Doug

Where Next?

Popular Pragmatic Bookshelf topics Top

jimmykiang
This test is broken right out of the box… — FAIL: TestAgent (7.82s) agent_test.go:77: Error Trace: agent_test.go:77 agent_test.go:...
New
jon
Some minor things in the paper edition that says “3 2020” on the title page verso, not mentioned in the book’s errata online: p. 186 But...
New
telemachus
Python Testing With Pytest - Chapter 2, warnings for “unregistered custom marks” While running the smoke tests in Chapter 2, I get these...
New
raul
Hi Travis! Thank you for the cool book! :slight_smile: I made a list of issues and thought I could post them chapter by chapter. I’m rev...
New
cro
I am working on the “Your Turn” for chapter one and building out the restart button talked about on page 27. It recommends looking into ...
New
brian-m-ops
#book-python-testing-with-pytest-second-edition Hi. Thanks for writing the book. I am just learning so this might just of been an issue ...
New
jgchristopher
“The ProductLive.Index template calls a helper function, live_component/3, that in turn calls on the modal component. ” Excerpt From: Br...
New
taguniversalmachine
Hi, I am getting an error I cannot figure out on my test. I have what I think is the exact code from the book, other than I changed “us...
New
mcpierce
@mfazio23 I’ve applied the changes from Chapter 5 of the book and everything builds correctly and runs. But, when I try to start a game,...
New
roadbike
From page 13: On Python 3.7, you can install the libraries with pip by running these commands inside a Python venv using Visual Studio ...
New

Other popular topics Top

ohm
Which, if any, games do you play? On what platform? I just bought (and completed) Minecraft Dungeons for my Nintendo Switch. Other than ...
New
AstonJ
If you are experiencing Rails console using 100% CPU on your dev machine, then updating your development and test gems might fix the issu...
New
mafinar
Crystal recently reached version 1. I had been following it for awhile but never got to really learn it. Most languages I picked up out o...
New
First poster: joeb
The File System Access API with Origin Private File System. WebKit supports new API that makes it possible for web apps to create, open,...
New
New
New
AstonJ
Curious what kind of results others are getting, I think actually prefer the 7B model to the 32B model, not only is it faster but the qua...
New
NewsBot
Node.js v22.14.0 has been released. Link: Release 2025-02-11, Version 22.14.0 'Jod' (LTS), @aduh95 · nodejs/node · GitHub
New
PragmaticBookshelf
Fight complexity and reclaim the original spirit of agility by learning to simplify how you develop software. The result: a more humane a...
New
Fl4m3Ph03n1x
Background Lately I am in a quest to find a good quality TTS ai generation tool to run locally in order to create audio for some videos I...
New

Sub Categories: