A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://neon.com/postgresql/postgresql-tutorial/postgresql-check-constraint below:

PostgreSQL CHECK Constraints

Summary: in this tutorial, you will learn about the PostgreSQL CHECK constraints and how to use them to constrain values in columns of a table based on a boolean expression.

In PostgreSQL, a CHECK constraint ensures that values in a column or a group of columns meet a specific condition.

A check constraint allows you to enforce data integrity rules at the database level. A check constraint uses a boolean expression to evaluate the values, ensuring that only valid data is inserted or updated in a table.

Typically, you create a check constraint when creating a table using the CREATE TABLE statement:

CREATE TABLE table_name(
   column1 datatype,
   ...,
   CONSTRAINT constraint_name CHECK(condition)
);

In this syntax:

If the CHECK constraint involves only one column, you can define it as a column constraint like this:

CREATE TABLE table_name(
   column1 datatype,
   column1 datatype CHECK(condition),
   ...,
);

By default, PostgreSQL assigns a name to a CHECK constraint using the following format:

{table}_{column}_check

To add a CHECK constraint to an existing table, you use the ALTER TABLE ... ADD CONSTRAINT statement:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

To drop a CHECK constraint, you use the ALTER TABLE ... DROP CONSTRAINT statement:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Let’s explore some examples of using the CHECK constraints.

First, create a new table called employees with some CHECK constraints:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR (50) NOT NULL,
  last_name VARCHAR (50) NOT NULL,
  birth_date DATE NOT NULL,
  joined_date DATE NOT NULL,
  salary numeric CHECK(salary > 0)
);

In this statement, the employees table has one CHECK constraint that enforces the values in the salary column greater than zero.

Second, attempt to insert a new row with a negative salary 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);

Error:

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).

The insert fails because the CHECK constraint on the salary column accepts only positive values.

First, use the ALTER TABLE ... ADD CONSTRAINT statement to add a CHECK constraint to the employees table:

ALTER TABLE employees
ADD CONSTRAINT joined_date_check
CHECK ( joined_date >  birth_date );

The CHECK constraint ensures that the joined date is later than the birthdate.

Second, attempt to insert a new row into the employees table with the joined date is earlier than the birth date:

INSERT INTO employees (first_name, last_name, birth_date, joined_date, salary)
VALUES ('John', 'Doe', '1990-01-01', '1989-01-01', 100000);

Output:

ERROR:  new row for relation "employees" violates check constraint "joined_date_check"
DETAIL:  Failing row contains (2, John, Doe, 1990-01-01, 1989-01-01, 100000).

The output indicates that the data violates the check constraint “joined_date_check”.

The following example adds a CHECK constraint to ensure that the first name has at least 3 characters:

ALTER TABLE employees
ADD CONSTRAINT first_name_check
CHECK ( LENGTH(TRIM(first_name)) >= 3);

In this example, we define a condition using the TRIM() and LENGTH() functions:

The whole expression LENGTH(TRIM(first_name)) >= 3 ensures the first name contains three or more characters.

The following statement will fail because it attempts to insert a row into the employees table with the first name that has 2 characters:

INSERT INTO employees (first_name, last_name, birth_date, joined_date, salary)
VALUES ('Ab', 'Doe', '1990-01-01', '2008-01-01', 100000);

Error:

ERROR:  new row for relation "employees" violates check constraint "first_name_check"
DETAIL:  Failing row contains (4, Ab, Doe, 1990-01-01, 2008-01-01, 100000).

The following statement removes the CHECK constraint joined_date_check from the employees table:

ALTER TABLE employees
DROP CONSTRAINT joined_date_check;

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4