Skip to main content

Postgres Check constraint notes

 

efine PostgreSQL CHECK constraint for new tables

Typically, you use the CHECK constraint at the time of creating the table using the CREATE TABLE statement.

The following statement defines an employees table.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
	id SERIAL PRIMARY KEY,
	first_name VARCHAR (50),
	last_name VARCHAR (50),
	birth_date DATE CHECK (birth_date > '1900-01-01'),
	joined_date DATE CHECK (joined_date > birth_date),
	salary numeric CHECK(salary > 0)
);Code language: SQL (Structured Query Language) (sql)

The  employees table has three CHECK constraints:

  • First, the birth date ( birth_date) of the employee must be greater than 01/01/1900. If you try to insert a birth date before 01/01/1900, you will receive an error message.
  • Second, the joined date ( joined_date) must be greater than the birth date ( birth_date). This check will prevent from updating invalid dates in terms of their semantic meanings.
  • Third, the salary must be greater than zero, which is obvious.

Let’s try to insert a new row into the employees table:

INSERT INTO employees (first_name, last_name, birth_date, joined_date, salary)
VALUES ('John', 'Doe', '1972-01-01', '2015-07-01', - 100000);Code language: SQL (Structured Query Language) (sql)

The statement attempted to insert a negative salary into the salary column. However, PostgreSQL returned the following error message:

[Err] ERROR:  new row for relation "employees" violates check constraint "employees_salary_check"
DETAIL:  Failing row contains (1, John, Doe, 1972-01-01, 2015-07-01, -100000).Code language: Shell Session (shell)

The insert failed because of the CHECK constraint on the salary column that accepts only positive values.

By default, PostgreSQL gives the CHECK constraint a name using the following pattern:

{table}_{column}_checkCode language: SQL (Structured Query Language) (sql)

For example, the constraint on the salary column has the following constraint name:

employees_salary_checkCode language: SQL (Structured Query Language) (sql)

However, if you want to assign aCHECK constraint a specific name, you can specify it after the CONSTRAINT expression as follows:

column_name data_type CONSTRAINT constraint_name CHECK(...)
Code language: SQL (Structured Query Language) (sql)

See the following example:

...
salary numeric CONSTRAINT positive_salary CHECK(salary > 0)
...Code language: SQL (Structured Query Language) (sql)

Define PostgreSQL CHECK constraints for existing tables

To add CHECK constraints to existing tables, you use the ALTER TABLE statement. Suppose, you have an existing table in the database named prices_list

CREATE TABLE prices_list (
	id serial PRIMARY KEY,
	product_id INT NOT NULL,
	price NUMERIC NOT NULL,
	discount NUMERIC NOT NULL,
	valid_from DATE NOT NULL,
	valid_to DATE NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Now, you can use ALTER TABLE statement to add the CHECK constraints to the prices_list table. The price and discount must be greater than zero and the discount is less than the price. Notice that we use a Boolean expression that contains the AND operators.

ALTER TABLE prices_list 
ADD CONSTRAINT price_discount_check 
CHECK (
	price > 0
	AND discount >= 0
	AND price > discount
);Code language: SQL (Structured Query Language) (sql)

The valid to date ( valid_to) must be greater than or equal to valid from date ( valid_from).

ALTER TABLE prices_list 
ADD CONSTRAINT valid_range_check 
CHECK (valid_to >= valid_from);Code language: SQL (Structured Query Language) (sql)

The CHECK constraints are very useful to place additional logic to restrict values that the columns can accept at the database layer. By using the CHECK constraint, you can make sure that data is updated to the database correctly.

In this tutorial, you have learned how to use PostgreSQL CHECK constraint to check the values of columns based on a Boolean expression.

Labels:

Comments

Post a Comment

Popular posts from this blog

Entity Relationship (ER) Diagram Model with DBMS Example

Reference :   Entity Relationship (ER) Diagram Model with DBMS Example What is ER Diagram? ER Diagram  stands for Entity Relationship Diagram, also known as ERD is a diagram that displays the relationship of entity sets stored in a database. In other words, ER diagrams help to explain the logical structure of databases. ER diagrams are created based on three basic concepts: entities, attributes and relationships. ER Diagrams contain different symbols that use rectangles to represent entities, ovals to define attributes and diamond shapes to represent relationships. At first look, an ER diagram looks very similar to the flowchart. However, ER Diagram includes many specialized symbols, and its meanings make this model unique. The purpose of ER Diagram is to represent the entity framework infrastructure. Entity Relationship Diagram Example Table of Content: What is ER Diagram? What is ER Model? History of ER models Why use ER Diagrams? Facts about ER Diagram Model ER Diagram...
Post a Comment
Read more

SQL Joins and advanced joins and Subqueries

  Refernce :  Expert Guide to Advanced SQL Joins: What You Need to Know It's helpful to visualize how these different SQL joins work. Here's a breakdown in a table-like format, along with explanations: SQL Join Types Overview Join Type Description Key Characteristics Use Cases INNER JOIN Returns rows where there is a match in both tables. - Shows only matching records. - Excludes unmatched rows from both tables. - Retrieving related data that exists in both tables. - Finding records with corresponding entries in another table. LEFT OUTER JOIN (LEFT JOIN) Returns all rows from the left table, and the matched rows from the right table. - Includes all records from the left table. - Fills in NULL values for columns from the right table where there's no match. - Retrieving all records from one table and their related data from another, even if some records don't have matches. - Finding records in one table that don't have corresponding entries in another. RIGHT OUTER JO...
Post a Comment
Read more

GIT BASH

  Bash Shell: Git Bash uses the Bash (Bourne Again SHell) command-line interpreter. This means you can use many of the same commands you'd find in a Linux or macOS terminal. Git Integration: Git Bash is tightly integrated with Git, making it easy to execute Git commands Essential Commands: Navigation: pwd : Prints the current working directory. ls : Lists files and directories in the current directory. cd <directory> : Changes the current directory. cd .. : Moves to the parent directory. File Management: mkdir <directory> : Creates a new directory. touch <file> : Creates a new file. rm <file> : Removes a file. rmdir <directory> : Removes an empty directory. Git Commands: git init : Initializes a new Git repository. git clone <repository URL> : Clones an existing Git repository. git status : Displays the status of your working directory. git add <file> : Adds a file to the staging area. git commit -m "commit message" : Commits chan...
Post a Comment
Read more