含羞草实验室研究所免费入口 含羞草实验室研究所免费 含羞草实验室研究所免费入口 含羞草实验室研究所免费 ,逢赌必输 末世重生之星球巨变小说 逢赌必输 末世重生之星球巨变小说

发布日期:2021年12月09日
PostgreSQL CHECK Constraint

In this section, we are going to understand the working of the PostgreSQL Check constraint, which is used to make sure that all values in a column or a field of a table satisfy particular situations.

The examples of the PostgreSQL check constraint, how to create a check constraint for new tables and existing tables.

What is PostgreSQL CHECK Constraint?

In PostgreSQL, the Check constraint can be defined by a separate name. It is used to control the value of columns being inserted.

It allows us to verify a condition that the value being stored into a record. If the statement is false, then the data disrupts the constraint which is not saved in the table.

In other words, we can say that the PostgreSQL CHECK constraint is used to make sure that all values in a column or a field of a table satisfy certain conditions.

For example, the check constraints must match a Boolean expression to calculate the values before they are inserted or updated to the column.

PostgreSQL will update or insert these values to the column if the values keep the check constraint. Or else, PostgreSQL will discard the modification and raise a constraint violation error.

The syntax for creating a CHECK Constraint using a create table statement

The below illustrations are used to display how we can create a CHECK Constraint using the CREATE TABLE command:

Description of Parameters

In the above syntax, we have used the following parameters, which we discussed in the below table:

Parameter Name Description
Table_name It is the name of a table which we are going to create.
column1, column2 These are columns that we created in the table.
constraint_name The constraint_name parameter is used to specify the name of the CHECK Constraint.
Column_name(s) These are the columns that are going to be a CHECK Constraint.

Example of PostgreSQL CHECK Constraint

To understand the PostgreSQ CHECK Constraint's working, we will see the below example.

When we are specifying the table's structure using the CREATE TABLE command, we can generally use the CHECK constraint.

In the below example, we create a new table called Worker, which contains multiple columns, such as Worker_ID, Worker_name, DOB, Joining_date, and Salary.

But, before creating the Worker table, we will use the DROP TABLE command to check whether a similar table is already existing in the Jtp database.

Output

After executing the above command, we will get the following window message: The Worker table does not exist.

Check Constraint

Now, we will create the Worker table using the CREATE command, as shown in the following statement:

Output

On executing the above command, we will get the below message window, which displays that the Worker table has been created successfully into the jtp database.

Check Constraint

In the above Worker table, we have used the following three CHECK constraints:

Check constraints Description
First constraint The Date of birth (DOB) of the Worker must be greater than 01/01/1900. If we try to insert the Date of birth before 01/01/1900, we will get an error message.
Second constraint The joined_date must be greater than the date of birth (DOB). And this particular check will prevent from modifying incorrect dates regarding their semantic meanings.
Third constraint The salary must be larger than zero.

After creating the Worker successfully, we will enter some values into it with the INSERT command's help.

Output

After implementing the above command, PostgreSQL issued the below error because we inserted a negative salary value (-300000) into the Salary column.

ERROR: new row for relation "worker" violates check constraint "worker_salary_check" DETAIL: Failing row contains (2, Mike Ross, 1985-10-01, 2018-11-23, -300000).

Check Constraint

And the insert command is failed due to the CHECK constraint on the Salary column, which takes only positive values.

By default, PostgreSQL provides the CHECK constraint a name with the help of the below pattern:

Let us see an example where the constraint含羞草实验室研究所免费入口 含羞草实验室研究所免费 含羞草实验室研究所免费入口 含羞草实验室研究所免费 ,逢赌必输 末世重生之星球巨变小说 逢赌必输 末世重生之星球巨变小说 on the Salary column has the below constraint name:

PostgreSQL CHECK constraint in a separate name

If we want to allocate the CHECK constraint a separate name, we can define it after the CONSTRAINT expression, as we can see in the following illustration:

Let us see one sample example to understand how we can use the separate name in a PostgreSQL Check constraint:

For this, we will create one new table as Orders using the CREATE command, as shown below:

Output

On executing the above command, we will get the following window message, which displays that the Orders table has been created successfully.

Check Constraint

If we want to see the constraint data dictionary, we can follow the below steps:

Step 1

Open the SQL shell (psql), which appeared with the necessary details. After that, we will log in to the Postgres database server using the password that we created during PostgreSQL's installation process.

After providing the password, we are connected to the Postgres server, as we can see in the below screenshot:

Check Constraint

Step 2

Now, we will connect to the specific database server jtp, which we created earlier with the help of the below command:


Check Constraint

Step 3

After that, we will execute the below syntax to list all constraints (Primary key, check, unique) of the particular table.

Finally, we are using the above command to see the constraints used for the Orders table:

Output

After implementing the above command, we will get the below result:

Check Constraint

Note: In the above Orders table, the Article_price column can not contain any value less than or equal to zero (0).

Here, a constraint name has been included so that the keyword CONSTRAINT is followed by the named constraint positive_Article_price followed by the expression.

Define PostgreSQL CHECK constraints for existing tables using an ALTER TABLE command

In PostgreSQL, we can add the PostgreSQL check constraint for the existing table with the ALTER TABLE command's help.

Assume that we have an existing table in the database, which is named as Amount_list.

Output

We will get the below message window after implementing the above command, which displays that the Amount_list table has been created successfully.

Check Constraint

To add the CHECK constraints to the Amount_list table, we can use the ALTER TABLE command, where the Amount and Discount more than zero, and the discount is less than the amount.

Note: We can also use a Boolean expression, which include the AND operators.

Output

After implementing the above command, we will get the below message window, which displays that the Amount_list table has been created successfully.

Check Constraint

And the Valid_to column values must be equal or greater to the Valid_from column values, as shown in the following command:

Output

On implementing the above command, we will retrieve the following message window, which displays that the specified table has been modified successfully.

Check Constraint

Note

  • The PostgreSQL Check constraints are very beneficial if we place the additional logic to restrict values.
  • And we can also ensure that the data is efficient to the database correctly with the help of the CHECK constraint.

Overview

In the PostgreSQL Check Constraint section, we have learned the following topics:

  • We have learned that the PostgreSQL CHECK constraint is used to check columns' values based on a Boolean expression.
  • We have used the CREATE TABLE command to create a Check constraint for the particular table.
  • We can also modify the PostgreSQL check constraint using the ALTER TABLE
  • We added a CHECK CONSTRAINT to the existing table with the ALTER TABLE command's help.
  • And we also understood the concept of using the PostgreSQL Check constraint with a separate name.





Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook 含羞草实验室研究所免费入口 含羞草实验室研究所免费 含羞草实验室研究所免费入口 含羞草实验室研究所免费 ,逢赌必输 末世重生之星球巨变小说 逢赌必输 末世重生之星球巨变小说 twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA