Data integrity and consistency should be top priorities for anyone who works with data. Wondering how can you enforce these practices in PostgreSQL? The
CHECK CONSTRAINT is ready to do work!
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and PostgreSQL 10.0 for these exercises.
- Discover the structure and data types of the
CHECK CONSTRAINTSto certain columns we deem necessary.
- Test those constraints with Inserts that fail the in-place checks.
CHECK CONSTRAINT allows you to stipulate the values allowed in certain columns depending on a Boolean expression that must evaluate to true for the data to be accepted.
To start, I will get a description of the
staging_assets table we will be working with:
<code>fab_tracking=> \d staging_assets;
Column | Type | Collation | Nullable | Default
pipe_id | character varying(25) | | not null |
kind | character varying(25) | | |
pipe_jt_num | character varying(25) | | |
pipe_heat | character varying(25) | | |
pipe_length | numeric(4,2) | | |
pipe_wall_thickness | numeric(4,3) | | | 0.000
degree | numeric(4,2) | | | 0
“staging_assets_pkey” PRIMARY KEY, btree (pipe_id)
“up_kind_chk” CHECK (upper(kind::text) = kind::text)
distinguish AFTER INSERT ON staging_assets FOR EACH ROW EXECUTE PROCEDURE move_along()</code>
We can see there is a
CHECK CONSTRAINT for the
kind column named
up_kind_chk. This constraint ensures that any value for this column must be in all upper-case letters (Takes out the guess-work on whether to search for upper-case, lower-case, mixed-case, etc..).
pipe_length column would be a great candidate for a
CHECK CONSTRAINT as well. Ensuring this column contains only values greater than zero is a solid requirement for what is accepted. After all, how can a piece of pipe not have a length?!?
Let’s create and apply that
CONSTRAINT now with PostgreSQL.
staging_assets table already exists, I must use the
ALTER TABLE command to add the
CONSTRAINT on this column.
CHECK CONSTRAINTS can be implemented during or after table creation. However, both methods involve syntax similar to:
column_name column_data_type CHECK (boolean expression) – PostgreSQL chooses a name for the
CONSTRAINT for you
column_name column_data_type CONSTRAINT custom_constraint_name CHECK (boolean expression) – you are naming the
For tables that already exist the syntax varies just a bit, using
ADD CONSTRAINT on the desired column(s).
In regards to the
(pipe_length > 0) is where the boolean expression is ensuring this value by saying ‘this column value must be greater than(
Now I will test the constraints out by inserting rows that should fail the relative checks.
Up first, I will purposely
INSERT a lower-case value for the
ERROR: new row for relation "staging_assets" violates check constraint "up_kind_chk"
DETAIL: Failing row contains (pipe-28, bend, 292A, 292Heat, 10.20, 0.299, 12.50).
bend that’s not in the required upper-case, into the
kind column, fails the boolean logic for the
Now using an upper value for this column:
INSERT 0 1
We should expect the same from the
pipe_len_chk constraint, however, to assume means making a….
We will just try it out and verify.
INSERT that should fail:
ERROR: new row for relation "staging_assets" violates check constraint "pipe_len_chk"
DETAIL: Failing row contains (Pipe-1128, PIPE_JOINT, 292A1, 292-Heat, 0.00, 0.694, 0.00).
As expected, that one fails too due to having a
pipe_length value not greater than zero. Finishing up, let’s run an
INSERT that will pass that same check:
INSERT 0 1
CHECK CONSTRAINTS to your table’s columns that require strict rules concerning acceptable values, for maintaining consistent data.
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Be sure and visit the “Best Of” page for a collection of my best blog posts.
To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.