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

johnp
Running the examples in chapter 5 c under pytest 5.4.1 causes an AttributeError: ‘module’ object has no attribute ‘config’. In particula...
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
simonpeter
When I try the command to create a pair of migration files I get an error. user=> (create-migration "guestbook") Execution error (Ill...
New
edruder
I thought that there might be interest in using the book with Rails 6.1 and Ruby 2.7.2. I’ll note what I needed to do differently here. ...
New
joepstender
The generated iex result below should list products instead of product for the metadata. (page 67) iex> product = %Product{} %Pento....
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
AufHe
I’m a newbie to Rails 7 and have hit an issue with the bin/Dev script mentioned on pages 112-113. Iteration A1 - Seeing the list of prod...
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
mert
AWDWR 7, page 152, page 153: Hello everyone, I’m a little bit lost on the hotwire part. I didn’t fully understand it. On page 152 @rub...
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

Other popular topics Top

Devtalk
Hello Devtalk World! Please let us know a little about who you are and where you’re from :nerd_face:
New
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
Or looking forward to? :nerd_face:
483 11975 256
New
brentjanderson
Bought the Moonlander mechanical keyboard. Cherry Brown MX switches. Arms and wrists have been hurting enough that it’s time I did someth...
New
AstonJ
We have a thread about the keyboards we have, but what about nice keyboards we come across that we want? If you have seen any that look n...
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
mafinar
This is going to be a long an frequently posted thread. While talking to a friend of mine who has taken data structure and algorithm cou...
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
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
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

Sub Categories: