Using Constraints in PostgreSQL
One of the most valuable assets in any company is their data. While database types add basic protection to guard against invalid data entry, we can use PostgreSQL constraints to enforce even more data validity checks. The great part about doing it at the database level is that it ensures that no matter what is adding or updating data the rules will be enforced.
You can create data constraints both at the table and column level. There are 6 different types of constraints found in PostgreSQL and we will go over a brief introduction to them in today's class.
NOTE: while almost all the examples in this tutorial create constraints on a single column, you can usually define constraints that apply to multiple columns at the same time.
So let's dig in and see how to use constraints.
We have loaded up a PostgreSQL database with no data so we can create schemas with constraints and then try to load data. Here are the details on the database we are connecting to:
- Username: groot
- Password: password (same password for the postgres user as well)
- A database named: workshop
The people used for the data will be members of Guardians Of The Galaxy movie. You don't need to know the characters to understand the exercises, but it will help you appreciate my humor.
In this class you go received an introduction to PostgreSQL constraints - a very effective method to insure the integrity of your data. We covered:
- Check Constraints - make sure the data being entered meets criteria
- Not Null Constraints - make sure a NULL value is not entered in a column
- Unique Constraints - make sure there are no duplicate values in a column
- Primary Keys - insure each row in the database has a unique identifier
- Foreign keys - make sure parent-child table retain data integrity
- Exclusion Constraints - prevent data from "overlapping", either over a range or against a range of column values.
The easiest way to create these constraints is at table definition time, but we also added them to the tables, even after data was added to the table.
We hope you keep using Constraints for a better database experience.
Enjoy learning about PostgreSQL? Sign up for our newsletter and get the latest tips from us each month.
Using Constraints with Columns and Tables
Using Check constraints
The first type of database constraint we will cover is a check constraint. Like its name implies, a check constraint checks the value that is being inserted or updated against a pre-defined condition. If the data meets the condition then the insert or update can proceed. If not then an error is returned and the insert of update does not proceed.
NOTE: defining a check constraint on column or table which already has data checks the constraint against existing data. If you have existing data that violates the constraint then PostgreSQL will not allow you to create the constraint. You will need to correct your data first or add the NOT VALID keywords to your alter table statement.
Creating a check constraint
Let's go ahead and write a nice simple constraint on a column. Let's create the typical "people" example table with:
- id (Primary key and Identity Column)
- Family/last name
- Given/first name
Age is an integer >= 0 and <= 130 (until science changes things). There is no numeric type in PostgreSQL that is only positive integers so that's why we will need a constraint. FYI, Primary key is also a constraint but we will cover that in a later exercise.
Let's create our table:
create table people ( id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, family_name text, given_name text, age integer CHECK (age >= 0 AND age <= 120) );
We could have also done the age line as:
age integer CONSTRAINT age_check CHECK (age >= 0 AND age <= 120)
This syntax allows you to provide a name for your constraint but if you don't PostgreSQL will automatically create one for you.
Now if we try to insert a value less than or greater than our constraints we will get an error.
insert into people (family_name, given_name, age) values ('Quill', 'Peter', 250);
and you should get:
ERROR: new row for relation "people" violates check constraint "people_age_check" DETAIL: Failing row contains (1, Quill, Peter, 250).
Nice! You just prevented invalid data from getting into your table. Let's update our statement and put in a valid value:
insert into people (family_name, given_name, age) values ('Quill', 'Peter', 36);
Text with length
Unlike many other RDBMSs, there is little difference under the hood for CHAR(N), VARCHAR(N), and TEXT. There is no space or performance penalty for using TEXT as compared to the other types. Therefore, many PostgreSQL experts recommend using TEXT, and if you need to enforce a length restriction just use a constraint. Let's go ahead and add a Twitter username to our table, which must be less <= 15 characters.
alter table people add column twitter_username text check ( char_length(twitter_username) < 16);
As you can see you can also use any of the PostgreSQL functions appropriate to the column type in your constraints.
If we now try to insert a value longer than the 15 characters we get a nice error message:
insert into people (family_name, given_name, age, twitter_username) values ('Udonta', 'Yondu', 59, 'thisismorethanfifteencharacters');
 ERROR: new row for relation "people" violates check constraint "people_twitter_username_check" Detail: Failing row contains (3, Udonta, Yondu, 59, thisismorethanfifteencharacters).
Let's add a valid value now:
insert into people (family_name, given_name, age, twitter_username) values ('Udonta', 'Yondu', 59, 'mr_mohawk');
The constraints we made above are column constraints because they only apply to a single column. PostgreSQL also has the ability to make table constraints, which are attached to the table rather than an individual column. For our users we don't want their Twitter handle to be any part of their family name:
alter table people add constraint no_name_twitter check ( strpos(lower(twitter_username), lower(family_name)) = 0);
The strpos function returns the location of the specified substring if the substring is present and 0 otherwise. And if we try to put in invalid data:
insert into people (family_name, given_name, age, twitter_username) values ('Raccoon', 'Rocket', 19, 'mr_raccoon2u');
 ERROR: new row for relation "people" violates check constraint "no_name_twitter" Detail: Failing row contains (6, Raccoon, Rocket, 19, mr_raccoon2u).
Now let's put in a valid entry:
insert into people (family_name, given_name, age, twitter_username) values ('Raccoon', 'Rocket', 19, 'trashpanda');
For more complicated logic checks you can write your own function in this place. For example, if you wanted to check that nobody under 18 years of age has a Twitter username, you write a function that takes two values, an integer for age and a text for username, and then returns true if there is NULL for text and an age < 18. You could then call that function with your columns in the constraint.
To see all the constraints we have made on our table you simply have to use the
\d <tablename> command.
You can see that when PostgreSQL returns the table statements it shows all the constraints in a section on the bottom of the output titled "Check Constraints". You can also see that PostgreSQL created the name "people_age_check" for our unnamed check constraint on the on the age column.
The choice to make your check constraints table or column constraints is only a matter of preference, it makes no difference to functionality or performance in the database.
Removing a constraint is as easy as:
alter table <table name> drop constraint <constraint name>
Right now we have no need to drop a constraint so we will let it be.
In this section we covered the basics of constraints, check constraints in particular, the difference between column and table constraints, how to list the constraints on a table, and how to delete a constraint.
In this next section we will cover UNIQUE and NOT NULL Constraints.