Data integrity is an important aspect for any database system, ensuring that stored data remains accurate, consistent, and meaningful. In PostgreSQL, a powerful tool for maintaining data integrity is to use check constraints. These constraints allow you to define the rules that data must follow to prevent the insertion or modification of invalid data.
Understanding Check Constraints
Check constraints are rules that restrict the values that can be input into a column or a set of columns in a table. These rules are enforced by the database system to prevent the insertion or modification of rows that violate specified conditions. The check constraint is defined using the CHECK keyword, followed by an expression that evaluates to a Boolean value.
Verify lease term
Suppose there is a scenario that uses a modified version of the "rental" table in the "dvrental" database, which contains a "rental_duration" column. In the Navicat Table Designer, the definition of this table may be as follows:
Now, let's assume we want to ensure that the duration of each lease is always greater than zero days. We can achieve this goal by adding check constraints in the "rentals_with_rental_period" table, as follows:
In Navicat, we can add a check constraint in the Check tab of the Table Designer. We only need to provide an expression and an optional name. Navicat will create a unique name for us
In Navicat, we can add check constraints in the Check tab of Table Designer. We only need to provide an expression and an optional name. If we don't provide a name, Navicat will create a unique name for us!
By executing this constraint, any attempt to insert or update a row in the "rentals_with_rental_period" table with a lease duration less than or equal to zero will result in an error, ensuring that only valid lease durations are allowed.
Ensure effective rating
Another example of the "film" table in the "dvrent" database involves verifying movie ratings. Assuming we want to limit the rating to certain specific values, such as "G", "PG", "PG-13", "R", or "NC-17". We can achieve this by checking constraints:
In the Navicat Table Designer, this is the same constraint:
Now, any attempt to insert or update ratings in the "film" table with values other than one of the specified values will be rejected, ensuring that only valid ratings are allowed.
Handle null values
It should be noted that unless the constraint specifically includes a condition for checking for NULL, the check constraint will not be applied to one or more rows containing NULL values. For example, to force the "rental_rate" column in the "film" table to always be greater than zero and not NULL, we will use the following constraints:
In the Navicat Table Designer, the same constraints are as follows:
