How PostgreSQL Check Constraints Reduce Your Development Time

In the comments around the last post here on Malt Blue, why Kohana is an excellent alternative to the Zend Framework, which received quite a bit of attention socially as well, there was a bit of attention focused on the database layer.

Specifically:

Lubos said:

Very similar to Kohana is Yii, however it offers significantly more robust functionalities - ORM based on PDO supports 5 RDBMS, web2 widgets based on JQuery UI, built-in support for integration with 3rd party libraries.

and hussainweb said:

I have never noticed issues with the ORM (I almost always use MySQL) and as far as widgets are concerned, I have written a little abstraction over views to make it easier to handle such scenarios.

Also, in the tweets around it, @aguimaraes1986 asked about some posts on alternative databases, especially PostgreSQL. So given that, I thought that this post would start to introduce a bit of a database thread to the blog.

So in this week’s post, we’re going to have a look at how a simple feature in PostgreSQL can reduce your development time.

Check Constraints

Specifically, we’re going to be looking at check constraints. Despite the plethora of alternative databases to MySQL and despite the mixed opinion about where it is going, after the acquisition by Oracle in January 2010, it’s still used with an enormous amount of PHP-related software, by default; whether it’s Joomla, Typo3, WordPress, phpBB, Drupal or a wide selection of others.

Now this isn’t a shot at MySQL. But in this writers humble opinion, despite all the great features of it, PostgreSQL is superior. Why? Well, without wanting to get in to a flame war over this, it’s a more rounded product that allows us as developers to avoid re-inventing the wheel repeatedly.

But what are check constraints? To quote the PostgreSQL (8.4) documentation check constraints:

allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression

In other words, you have the ability to ensure that the value stored in a table column is within an acceptable range. For example:

  • If the column stores a person’s gender, then the value entered is ’M' or ‘F’
  • If the column stores a yes/no value, then the value stored is either ‘Y’ or ‘F’
  • That a price is less than a set value, within a range of values or greater than a set value
  • If you’re storing database choice, that the range is within the options you provide

Admittedly, these are simple examples. But you can see how it’s analogous to a MySQL ENUM type on steroids (if you will) - much more flexible and accommodating.

How Can We Use Them?

The best thing about it, is that you already can. You don’t need to look for a special function in either the PHP PostgreSQL or PDO functions. With either command-line or GUI access to a running PostgreSQL database, you can start today!. Let’s go through how with a simple example.

Creating Check Constraints

What we’re going to do is create a table that stores users, only if they’re over 18 years of age - say one to ensure that we don’t sell alcohol and cigarettes to minors (using the Australian laws).

We’ll create a table that simply stores 4 criteria about a user:

  • first - character varying(150)
  • last - character varying(150)
  • emailaddress - character varying(200)
  • dateOfBirth - date

Based on the dateOfBirth column, we’ll create a basic check constraint that ensures that we can only enter a user if the year of their date of birth is 18 years before the current year. Word to the wise, this isn’t a perfect solution - care to comment why?

The basic table structure is as follows:

1
2
3
4
5
6
7
8
9
10
11
mbtest=> \d tbl_users;
         Table "public.tbl_users"
Column    |          Type          | Modifiers
--------------+------------------------+-----------
 id           | bigint                 | not null
 first        | character varying(150) | not null
 last         | character varying(150) |
 emailaddress | character varying(200) |
 dateOfBirth  | date                   | not null
Indexes:
    "tbl_users_pkey" PRIMARY KEY, btree (id)

Here in our database, mbtest, we've created a table called tbl_users. You can see the schema above. Now we’re going to create a check constraint to enforce our business logic. Have a look at the statement below:

1
2
3
4
5
6
7
mbtest=> alter table tbl_users add constraint is_over_18 check (
    (
        DATE_PART('year', "dateOfBirth"::timestamp) <
        DATE_PART('year', now() - interval '18 years')
    )
);
ALTER TABLE

Allowing for formatting for readability, what it does is:

  1. Cast the value being entered for the dateOfBirth column and extract the year component
  2. Compare that year to the year 18 years before the current one

If the users date of birth year is less than that 18 years ago, then we accept the value, creating the record. Otherwise, we reject it with an error, such as the one below:

1
ERROR:  new row for relation "tbl_users" violates check constraint "is_over_18"

After we’ve altered our table, we’ll have a schema that looks like below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mbtest=> \d tbl_users;
         Table "public.tbl_users"
Column    |          Type          | Modifiers
--------------+------------------------+-----------
 id           | bigint                 | not null
 first        | character varying(150) | not null
 last         | character varying(150) |
 emailaddress | character varying(200) |
 dateOfBirth  | date                   | not null
Indexes:
    "tbl_users_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "is_over_18" CHECK (
        date_part('year'::text, "dateOfBirth"::timestamp without time zone) <
        date_part('year'::text, now() - '18 years'::interval)
    )

In its simplest form, that’s it. A bit of logic in the constraint and away we go.

Less Code Required

Now, we could write a filter/validator in our code, such as using a combination of PHP DATETIME functions and the Zend_Validate::GreaterThan validator. But well, it’s going to be quicker in the database.

As I said, is a rather simple example. You can do a hell of a lot more if you set your imagination to the task. But I hope that this, brief, post today has at least given you some inspiration about what you can do with PostgreSQL’s check constraints to lessen the development load.

If you’d like to read more about today’s post, check out these excellent links for further reading:



About Matthew

Matthew Setter Matthew Setter is a PHP & Zend Framework specialist. If you're in need of a custom software application, need to migrate an existing legacy application, or want to know your current application's GPA - get in touch.

Want To Be A Zend Framework Guru?

Drop your email in the box below, and get awesome tutorialsjust like this one — straight to your inbox, PLUS exclusive content only available by email.