Constraints - (PostgreSQL Constraints)

Constraints are rules that are used to restrict the values that can be inserted into a table. They are used to maintain data integrity by ensuring that the data in the table meets certain conditions.

In this tutorial, we'll cover the syntax, example, output, explanation, use, important points, and summary of constraints in PostgreSQL.

Syntax

CREATE TABLE table_name (
  column1 datatype constraint,
  column2 datatype constraint,
  column3 datatype constraint,
  ...
);

Example

Let's create a table with constraints using the following SQL statement:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL NOT NULL CHECK (price > 0),
  stock INT NOT NULL CHECK (stock >= 0)
);

This SQL statement creates a table called products with columns for id, name, price, and stock. The id column is the primary key, which ensures that each row is unique. The name column is not allowed to be null, so it must have a value for each row. The price and stock columns are also not allowed to be null, and they both have check constraints to ensure that their values are greater than or equal to zero.

Explanation

In the above example, we used the CREATE TABLE statement to create a new table called products. We defined four columns: id, name, price, and stock. The id column is defined as a SERIAL data type, which automatically increments for each new row inserted into the table. The name column is defined as a VARCHAR(255) data type and is not allowed to be null. The price column is defined as a DECIMAL data type and is not allowed to be null. It also has a check constraint that ensures that its value is greater than 0. The stock column is defined as an INT data type and is not allowed to be null. It also has a check constraint that ensures that its value is greater than or equal to 0.

Use

Constraints are used to ensure that the data in a table meets certain conditions. They can help to prevent invalid data from being inserted into a table, thus maintaining data integrity.

Important Points

• A PRIMARY KEY constraint ensures that the values in a column are unique.
• A FOREIGN KEY constraint is used to create a relationship between two tables.
• A UNIQUE constraint ensures that the values in a column are unique, but allows null values.
• A CHECK constraint ensures that the values in a column meet a certain condition.
• A NOT NULL constraint ensures that a column has a value for each row.
• A DEFAULT constraint provides a default value for a column when no value is specified for a row.

Summary

In this tutorial, we covered the syntax, example, output, explanation, use, important points, and summary of constraints in PostgreSQL. Constraints are used to ensure that the data in a table meets certain conditions and can help to prevent invalid data from being inserted into the table. With this knowledge, you can now create tables with constraints to maintain data integrity in your PostgreSQL database.