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

mikecargal
Title: Hands-On Rust (Chap 8 (Adding a Heads Up Display) It looks like ​.with_simple_console_no_bg​(SCREEN_WIDTH*2, SCREEN_HEIGHT*2...
New
mikecargal
Title: Hands-On Rust (Chapter 11: prefab) Just played a couple of amulet-less games. With a bit of debugging, I believe that your can_p...
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
joepstender
The generated iex result below should list products instead of product for the metadata. (page 67) iex> product = %Product{} %Pento....
New
rmurray10127
Title: Intuitive Python: docker run… denied error (page 2) Attempted to run the docker command in both CLI and Powershell PS C:\Users\r...
New
jskubick
I think I might have found a problem involving SwitchCompat, thumbTint, and trackTint. As entered, the SwitchCompat changes color to hol...
New
New
kolossal
Hi, I need some help, I’m new to rust and was learning through your book. but I got stuck at the last stage of distribution. Whenever I t...
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

siddhant3030
I’m thinking of buying a monitor that I can rotate to use as a vertical monitor? Also, I want to know if someone is using it for program...
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
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
AstonJ
There’s a whole world of custom keycaps out there that I didn’t know existed! Check out all of our Keycaps threads here: https://forum....
New
AstonJ
In case anyone else is wondering why Ruby 3 doesn’t show when you do asdf list-all ruby :man_facepalming: do this first: asdf plugin-upd...
New
AstonJ
Seems like a lot of people caught it - just wondered whether any of you did? As far as I know I didn’t, but it wouldn’t surprise me if I...
New
Margaret
Hello everyone! This thread is to tell you about what authors from The Pragmatic Bookshelf are writing on Medium.
1139 25478 754
New
wmnnd
Here’s the story how one of the world’s first production deployments of LiveView came to be - and how trying to improve it almost caused ...
New
PragmaticBookshelf
Author Spotlight: Karl Stolley @karlstolley Logic! Rhetoric! Prag! Wow, what a combination. In this spotlight, we sit down with Karl ...
New

Sub Categories: