This is the second post on Postgres series, I recommend starting there to get familiar with some other important concept of PostgreSQL. But if you’re ready to dive deeper into data integrity, let’s get started!
In this post, we’re going to take a closer look at one of the most effective tools for ensuring data integrity in PostgreSQL: constraints. Specifically, I’ll show you how constraints can be used to enforce rules like ensuring prices are always positive or that discounts don’t exceed original prices—directly in the database.
In PostgreSQL, constraints play a crucial role in ensuring data integrity by enforcing specific rules on the data stored in your database. Constraints are a powerful way to ensure that the data meets certain conditions, preventing errors before they can affect your application.
One common example is ensuring that a price column only stores positive values, starting from 0. Unlike MySQL, which provides a `UNSIGNED` integer type to enforce this, PostgreSQL doesn’t have a built-in data type to store only positive numbers. Instead, you can use check constraints to enforce this rule.
Why Constraints Matter
With constraints, you can enforce rules directly in the database to ensure data consistency. For example:
- A
priceshould always be positive. - A
discount_priceshould always be less than theprice. - - An
abbr(abbreviation)should always have exactly 5 characters.
These constraints help avoid the risk of invalid data entering the database, whether it’s from direct inserts, updates, or even malicious attempts to bypass application-level checks.
Examples of Constraints
Let’s look at some examples to see how you can use constraints in PostgreSQL.
Example 1: Column-Level Constraints
You can apply constraints directly to individual columns to ensure they meet certain conditions. Here’s an example where we define a table with three columns: price, discount_price, and abbr.
CREATE TABLE checks_example (
price NUMERIC
CONSTRAINT price_must_be_positive CHECK (price > 0),
-- Column-level check discount_price NUMERIC
CONSTRAINT discount_price_must_be_positive CHECK (discount_price > 0),
abbr TEXT
CONSTRAINT abbr_length_check CHECK (LENGTH(abbr) = 5)
);Here, we’ve added the following constraints:
price_must_be_positive: Ensures that thepriceis always greater than 0.discount_price_must_be_positive: Ensures thatdiscount_priceis also greater than 0.abbr_length_check: Ensures thatabbrhas exactly 5 characters.
Now, if you try to insert a row where abbr is shorter than 5 characters, PostgreSQL will throw an error:
INSERT INTO checks_example(price, discount_price, abbr)
VALUES (10, 10, 'abcd');
- ERROR: new row for relation "checks_example" violates check constraint "abbr_length_check"Example 2: Table-Level Constraints
You can also define constraints that involve multiple columns.
These are table-level constraints. For instance, we might want to ensure that the discount_priceis always less than the price.
CREATE TABLE checks_example (
price NUMERIC CHECK (price > 0),
discount_price NUMERIC
CONSTRAINT discount_must_be_positive CHECK (discount_price > 0),
abbr TEXT
CONSTRAINT abbr_length_check CHECK (LENGTH(abbr) = 5),
CONSTRAINT discount_price_must_be_less_price CHECK (price > discount_price)
-- discount_price_must_be_less_price - TABLE LEVEL CONSTRAINT
);Here, the discount_price_must_be_less_priceconstraint ensures that the discount_price is always less than the price. This is a table-level constraint because it involves multiple columns.
Inserting Data and Error Handling
Let’s try inserting some rows into the table:
- This will fail because discount_price (15) is greater than price (10)
INSERT INTO checks_example(price, discount_price, abbr)
VALUES (10, 15, 'abcde');
- ERROR: new row for relation "checks_example" violates check constraint "discount_price_must_be_less_price"
- This works because discount_price (8) is less than price (10)
INSERT INTO checks_example(price, discount_price, abbr)
VALUES (10, 8, 'abcde');
- SuccessAs you can see, PostgreSQL raises an error if the discount_price exceeds the price, enforcing the rule we defined in the table-level constraint.
Named Constraints for Clarity
You may have noticed that some constraints have names, like price_must_be_positiveand discount_must_be_positive. Naming your constraints is a best practice because it makes error messages more informative and easier to troubleshoot. If a constraint fails, the name of the violated constraint will appear in the error message, which makes debugging much simpler.
Conclusion
In summary, PostgreSQL’s check constraints are a powerful tool for ensuring data integrity at the database level. They allow you to enforce rules like positivity for prices, relationships between columns, and specific formats for text fields, all directly within the database schema. While it’s important to perform validation in your application, using constraints in the database provides an additional layer of protection, ensuring that your data remains consistent and reliable, even when accessed directly.
By using check constraints, you’re not just ensuring data integrity; you’re also embedding business rules into the database, making it harder for invalid or inconsistent data to sneak through — whether via application logic, manual database manipulation, or integration with other systems.
Final Thoughts
While application-level checks are important, database constraints offer a critical second line of defence. They ensure that data follows business rules consistently and reliably. Remember, no one is safe from the possibility of bypassing application logic, and that’s where constraints step in to help safeguard your data.
If you found this post helpful, I’d really appreciate a thumbs up or a comment to let me know! Your feedback helps me create more content that’s useful to you.
And if you want to stay updated on the next posts in this PostgreSQL series (and many more database tips), don’t forget to subscribe to my blog. I’ve got a lot more valuable insights coming your way.
Thanks for reading, and Happy Coding!
