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_assetstarget table. - Apply
CHECK CONSTRAINTSto 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 INSERTthe 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.