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.
Objectives.
- Discover the structure and data types of the
staging_assets
target table. - Apply
CHECK CONSTRAINTS
to certain columns we deem necessary. - Test those constraints with Inserts that fail the in-place checks.
A 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:
Table "public.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
Indexes:
"staging_assets_pkey" PRIMARY KEY, btree (pipe_id)
Check constraints:
"up_kind_chk" CHECK (upper(kind::text) = kind::text)
Triggers:
distinguish AFTER INSERT ON staging_assets FOR EACH ROW EXECUTE PROCEDURE move_along()
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..).
The 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.
Since the staging_assets
table already exists, I must use the ALTER TABLE
command to add the CONSTRAINT
on this column.
ALTER TABLE
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
or
column_name column_data_type CONSTRAINT custom_constraint_name CHECK (boolean expression)
– you are naming the CONSTRAINT
yourself.
TIP:
For tables that already exist the syntax varies just a bit, using ADD CONSTRAINT
on the desired column(s).
In regards to the pipe_len_chk
CONSTRAINT
, (pipe_length > 0)
is where the boolean expression is ensuring this value by saying ‘this column value must be greater than(>
) zero’.
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 kind
column.
VALUES('pipe-28','bend','292A','292Heat',10.2,0.299,12.50);
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).
Attempting to INSERT
the value bend
that’s not in the required upper-case, into the kind
column, fails the boolean logic for the up_kind_chk
CHECK CONSTRAINT
.
Now using an upper value for this column:
VALUES('pipe-28','BEND','292A','292Heat',10.2,0.299,12.50);
INSERT 0 1
Success!!
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.
Below, another INSERT
that should fail:
VALUES('Pipe-1128','PIPE_JOINT','292A1','292-Heat',0,0.694,0);
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:
VALUES('Pipe-1128','PIPE_JOINT','292A1','292-Heat',16.33,0.694,0);
INSERT 0 1
Again success!
Try adding 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). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.