PostgreSQL— Constraints

Hi everyone, in this blog, we will learn about the constraints in PostgreSQL.

Constraints are the rules enforced on data columns on table. These are used to prevent invalid data from being entered into the database.

Constraints could be column level or table level. Column level constraints are applied only to one column whereas table level constraints are applied to the whole table.

Here are the commonly used constraints available in PostgreSQL.

1. NOT NULL Constraint — Ensures that a column cannot have NULL value.
2. UNIQUE constraint — Ensures that all values in a column are different.
3. PRIMARY key — Uniquely identifies each row/record in a database table.
4. FOREIGN key — Constrains data based on columns in other tables.
5. CHECK Constraint — The CHECK constraint ensures that all values in a column satisfy certain conditions.

NOT NULL Constraint:

By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL value is not allowed for that column.

In the above example, the column names namely ID, NAME, AGE has the constraint NOT NULL which means the values in the column cannot be null.

UNIQUE Constraint:

The UNIQUE Constraint prevents two records from having identical values in a particular column.

In the above example no two people can have same email id. So that column is set as Unique.

PRIMARY Key Constraint:

The Primary Key Constraint uniquely identifies each record in a database table. There can be more UNIQUE columns, but only one Primary key in a table. These Primary key are important when designing the database tables. Primary keys are unique ids.

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

In the above table the ID column is set as Primary key, usually the ID will be unique so it is always easy to identify the particular record.

FOREIGN KEY Constraint:

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. They are called foreign keys because the constraints are foreign; that is, outside the table. Foreign keys are sometimes called a referencing key.

In the above example, there are two tables namely COMPANY1 and the DEPARTMENT. The ID from the COMPANY1 table is used as the FOREIGN key in the DEPARTMENT table.

CHECK Constraint:

The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and is not entered into the table.

In the above example, when the query is executed it will create a new table and will check for the SALARY column, if the value is greater than 0, then that particular row is entered into the table.

Hope you found it useful. Happy learning!!