@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.
1 Like
Hi @douglasshuang! I see you created a few issues. First, thank you for doing that. That helps us improve the book. Second, sorry for missing these for months. I guess I’m not getting notifications about these, and I hadn’t been in here for a while. I have a big batch of planned updates based on questions.
I’ll be back with a response specific to your entry here. I’m going to review all of them, pull up the book text and source code, and see what we can do. Thanks!
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
Hi @andatki,
Thank you for all of your responses! Your planned changes look good.
Doug
