What are database constraints?

Database constraints are mechanism for defining rules for data in tables. Most developers are familiar with constraints like:

• NOT NULL constraint to prevent null values,
• UNIQUE constraint to prevent duplicate values,
• PRIMARY and FOREIGN KEY constraints for specifying keys.

These are just few examples, but the constraint that we will have a closer look is called CHECK constraint.

Check constraints are used to define specific conditions, that need to be satisfied in order to successfully store value in the database column. If the condition is violated, the operation is aborted. Example for those rules may be specifying that a number needs to be within specific range, or specifying accepted values for string column.

This can be especially useful when working with PHP enums backed by strings. Since introducing enums in PHP 8.1, it became quite popular to store enums as strings in the database. This works great together with Laravel casts, but can introduce bugs if the incorrect value makes it to the database. Check constraints are therefore a great addition to string backed enums.

Request validation vs check constraint

Database check constraints may seem very similar to request validation. While they serve the same purpose - ensuring the stored data is correct - they work on different layers. A request validation works on the application layer, while the check constraints work on database layer. Validation can be sufficient for most cases, but bug may be eventually introduced by developers, especially in large applications. If the incorrect data bypass the validation, it will be stored in the database and will cause more bugs and unwanted behaviour in the future. Check constraints are therefore a great addition to the request validation and they complement each other.

How to define check constraints in Laravel

While Laravel provides many built in methods for interacting with database and defining the database structure, there is currently no method to simplify the definition of the check constraints. Luckily for us, the syntax for defining the constraints in MySQL and Postgres is simple enough. SQLite does support check constraints, however they can be defined only when creating a table. For purposes of this article, we will focus on the MySQL and Posgres.

Let’s demonstrate on the following example of the order status column, that is handled by PHP string backed enum:

1enum OrderStatus: string {
2    case CREATED = 'created';
3    case SHIPPED = 'shipped';
4    case COMPLETED = 'completed';
5}

We have a database column (VARCHAR) for orders table called, you guessed it, status. To define a check constraint, that will accept only created, shipped or completed as values, we need to run following SQL:

1ALTER TABLE orders
2ADD CONSTRAINT check_status
3CHECK (status IN ("created", "shipped", "completed"))

In Laravel, we can wrap this in DB::statement helper:

1DB::statement('
2ALTER TABLE orders
3ADD CONSTRAINT check_status
4CHECK (status IN ("created", "shipped", "completed"))
5');

A good addition would be to remove any previous constraints with same name, so we won’t run into any issues:

 1// postgres 
 2DB::statement("ALTER TABLE orders
 3DROP CONSTRAINT IF EXISTS check_status");
 4 
 5// mysql
 6DB::statement("ALTER TABLE orders
 7DROP CONSTRAINT check_status");
 8 
 9DB::statement('
10ALTER TABLE orders
11ADD CONSTRAINT check_status
12CHECK (status IN ("created", "shipped", "completed"))
13');

Finally, we can add this code to migration, or even better - we can create a command, that will update the constraints during every deployment (quick note: I am currently working on the package that will handle updating constraints dynamically from your enums, stay tuned on my Twitter and LinkedIn).

This way, we can be sure, that any unwanted values will be stopped from being inserted into our status column. And since it works on the database level, the column is guarded also from direct SQL insertions of incorrect values.