What are Check Constraints?

In PostgreSQL, check constraints are a powerful tool to enforce certain conditions on the data in your tables. Unlike enums, which define a strict set of allowed values, check constraints give you more flexibility to define custom rules for columns.

Check constraints can be used to achieve similar functionality as enums – validating input against a specific list of values – while offering more control over data integrity and validation logic. You can even use them to manage multiple allowed values, mimicking some array functionality.

In this article, we’ll walk through how to use check constraints to validate values in PostgreSQL and how this approach compares to enums and arrays.

Defining Check Constraints in PostgreSQL

Let’s start by defining a simple table with a check constraint.

For example, say we want to define a card_type column that only allows certain values ('visa', 'mastercard', and 'amex'). Instead of using an enum type, we can use a check constraint to enforce this validation directly in the table definition.

CREATE TABLE payment (
  name text,
  card_type text CHECK (card_type IN ('visa', 'mastercard', 'amex'))
);

In this example, the check constraint ensures that only the values 'visa', 'mastercard', and 'amex' are allowed in the card_type column.

Inserting Data

Let’s try inserting some valid and invalid data:

INSERT INTO payment VALUES ('grocery', 'visa');
INSERT INTO payment VALUES ('dinner', 'mastercard');
SELECT * FROM payment;

 name    | card_type
 --------+-------------
 grocery | visa
 dinner  | mastercard

If we attempt to insert an invalid value, PostgreSQL will throw an error:

INSERT INTO payment VALUES ('dinner', 'maestro');

ERROR:  new row for relation "payment" violates check constraint "payment_card_type_check"
DETAIL:  Failing row contains (dinner, maestro).

With check constraints, PostgreSQL enforces the allowed values based on the rule we defined. This is similar to using enums but with added flexibility, as you can extend or modify the constraint rule more easily.

Multiple Values with Check Constraints

What if we need to allow multiple card types for a single user, like we did with arrays in the previous article? While arrays give us an easy way to store multiple values, we can also use a check constraint to enforce validation across multiple values.

First, let’s define a table that allows multiple card types using an array of text values:

CREATE TABLE payment_array (
  name text,
  card_types text[] CHECK (
    card_types <@ ARRAY['visa', 'mastercard', 'amex']
  )
);

Here, we use the <@ operator to check that all the values in the card_types array are valid ('visa', 'mastercard', or 'amex').

Inserting and Querying Multiple Values

Now, let’s insert some data where a user has multiple card types:

INSERT INTO payment_array VALUES ('grocery', ARRAY['visa']);
INSERT INTO payment_array VALUES ('dinner', ARRAY['mastercard']);
INSERT INTO payment_array VALUES ('lunch', ARRAY['visa', 'amex']);
SELECT * FROM payment_array;

 name    | card_types
 --------+---------------------
 grocery | {visa}
 dinner  | {mastercard}
 lunch   | {visa,amex}

This check constraint ensures that only the allowed card types ('visa', 'mastercard', 'amex') can be inserted into the array, similar to the validation provided by enums, but with more flexibility.

Invalid Insertion

If we attempt to insert a value outside the allowed set, PostgreSQL throws an error:

INSERT INTO payment_array VALUES ('snacks', ARRAY['maestro']);

ERROR:  new row for relation "payment_array" violates check constraint "payment_array_card_types_check"
DETAIL:  Failing row contains (snacks, {maestro}).

This shows how check constraints can ensure data integrity, even when allowing multiple values.

Extending Check Constraints

One of the biggest advantages of using check constraints is their flexibility. You can modify the constraint at any time without redefining the column type, as would be required with enums.

For example, if your business expands to support a new card type ('discover'), you can easily modify the check constraint:

ALTER TABLE payment_array
  DROP CONSTRAINT payment_array_card_types_check,
  ADD CONSTRAINT payment_array_card_types_check
  CHECK (card_types <@ ARRAY['visa', 'mastercard', 'amex', 'discover']);

Now, the table allows discover as a valid value:

INSERT INTO payment_array VALUES ('snacks', ARRAY['discover']);

 name    | card_types
 --------+---------------------
 snacks  | {discover}

Unlike enums, which require redefining the type to accommodate new values, check constraints offer a much more flexible approach to validation.

Advantages of Check Constraints over Enums

• Flexibility: Check constraints can be updated or expanded without changing the underlying column type.
• Customization: You can create more complex rules using check constraints (e.g., combining multiple conditions or even performing regular expression matching).
• No Custom Types: With check constraints, you can work directly with standard data types (like text or integer), making schema changes easier.

When to Use Check Constraints vs Enums

• Use check constraints when:
  • You need flexible or complex validation rules.
  • You anticipate frequent changes to the allowed values.
  • You prefer to work with standard data types (e.g., text, integer).
• Use enums when:
  • You need strict type validation and type-based behavior.
  • You want a more structured approach to managing fixed sets of values.
  • You prefer type-level enforcement rather than column-level enforcement.

Conclusion

Check constraints in PostgreSQL provide a powerful and flexible way to validate data, offering many of the benefits of enums with additional customization options. Whether you’re enforcing single-value validation or working with arrays, check constraints can help you maintain data integrity with ease.

In PostgreSQL 16, the performance of check constraints has been further optimized, making them a robust choice for both small and large datasets. They allow you to create custom validation rules, adapt to changing requirements, and handle multiple values efficiently.

If you’re looking for more flexibility than enums or need to enforce more complex validation, check constraints are a great alternative.

Have any questions, want to share your thoughts or just say Hi? I’m always excited to connect! Follow me on Twitter or LinkedIn for more insights and discussions. If you’ve found this valuable, please consider sharing it on your social media. Your support through shares and follows means a lot to me!