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!

Where Next?

Popular Pragmatic Bookshelf topics Top

jesse050717
Title: Web Development with Clojure, Third Edition, pg 116 Hi - I just started chapter 5 and I am stuck on page 116 while trying to star...
New
jdufour
Hello! On page xix of the preface, it says there is a community forum "… for help if your’re stuck on one of the exercises in this book… ...
New
fynn
This is as much a suggestion as a question, as a note for others. Locally the SGP30 wasn’t available, so I ordered a SGP40. On page 53, ...
New
adamwoolhether
I’m not quite sure what’s going on here, but I’m unable to have to containers successfully complete the Readiness/Liveness checks. I’m im...
New
Charles
In general, the book isn’t yet updated for Phoenix version 1.6. On page 18 of the book, the authors indicate that an auto generated of ro...
New
gorkaio
root_layout: {PentoWeb.LayoutView, :root}, This results in the following following error: no “root” html template defined for PentoWeb...
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
dachristenson
I just bought this book to learn about Android development, and I’m already running into a major issue in Ch. 1, p. 20: “Update activity...
New
dachristenson
@mfazio23 Android Studio will not accept anything I do when trying to use the Transformations class, as described on pp. 140-141. Googl...
New
dachristenson
I’ve got to the end of Ch. 11, and the app runs, with all tabs displaying what they should – at first. After switching around between St...
New

Other popular topics Top

dasdom
No chair. I have a standing desk. This post was split into a dedicated thread from our thread about chairs :slight_smile:
New
Exadra37
On modern versions of macOS, you simply can’t power on your computer, launch a text editor or eBook reader, and write or read, without a ...
New
AstonJ
I ended up cancelling my Moonlander order as I think it’s just going to be a bit too bulky for me. I think the Planck and the Preonic (o...
New
Rainer
Not sure if following fits exactly this thread, or if we should have a hobby thread… For many years I’m designing and building model air...
New
PragmaticBookshelf
“A Mystical Experience” Hero’s Journey with Paolo Perrotta @nusco Ever wonder how authoring books compares to writing articles?...
New
rustkas
Intensively researching Erlang books and additional resources on it, I have found that the topic of using Regular Expressions is either c...
New
PragmaticBookshelf
Rails 7 completely redefines what it means to produce fantastic user experiences and provides a way to achieve all the benefits of single...
New
AstonJ
If you want a quick and easy way to block any website on your Mac using Little Snitch simply… File > New Rule: And select Deny, O...
New
First poster: bot
Large Language Models like ChatGPT say The Darnedest Things. The Errors They MakeWhy We Need to Document Them, and What We Have Decided ...
New
sir.laksmana_wenk
I’m able to do the “artistic” part of game-development; character designing/modeling, music, environment modeling, etc. However, I don’t...
New

Sub Categories: