Database Constraints, But Why?

Protect your data like this duck protects its young!
Photo by Gary Bendig on Unsplash (Protect your data like this duck protects its young!)

The Problem(s)

Our backend currently relies on model validations to ensure the correct format of any data that reaches the database. This has worked great so far, but we’re at a point where we need to start using SQL to add and update data in the database in order to speed up performance. Or we need to interact with the database directly to perform bulk operations that are otherwise difficult to perform. Any modifications made in this manner will not pass through validations :(

The Solution: Database Constraints!

Database constraints are the perfect answer to the problem stated above. They also help protect our data in the event that we switch out part of our stack, specifically the backend application that interacts with the database (So many things can go wrong when we make big changes!).

Like model validations, database constraints allow us to declare what the data in a column should look like: does it follow a pattern? Should it be a certain length? Can it be empty? Is there a range of values it should fall within? What about uniqueness? And on and on. In the event that someone bypasses the model validations to add or modify data, that new data won’t be added unless it conforms to the database constraints, thus protecting the integrity of our data.

As a general rule, I like to ensure that my database constraints mirror my model validations, or vice versa. At times, this means I need to break out my old SQL friend when I’m writing migrations, since the out-of-the-box constraints (aka helpers) are limited regardless of what we’re using for the backend application.

A Rails Example

Here’s a quick example of a migration that includes database constraints to ensure that certain columns always contain data, are unique, and/or require a minimum character length:

create_table :bands do |t|
t.string :name, null: false, unique: true
t.integer :members, null: false
t.timestamps
end
execute <<-SQL
ALTER TABLE bands
ADD CONSTRAINT check_minimum_length
CHECK (LENGTH(name) > 2)
SQL

And this also has the added benefit of automatically indexing the name column, which allows for faster lookups! This would not be true with a model validation alone. Ooooh, benefits…

Quick note: please don’t index every column in every table: this is a bad idea. Please look into why if you don’t believe me.

Conclusion / PSA

If there’s even the slightest chance that you or someone else will directly modify data in the database, please, please, whip out those constraints! I know there are people out there who don’t, so you do you. Personally, I always use database constraints, we’re tight like that.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
coderSloth

coderSloth

Full-stack web dev, Master Instructor @ Flatiron School, Max programmer, Arduino chef, artist @ 84collective.com, linkedin.com/in/rupadhillon/, rupadhillon.com