Constraints in PostgreSQL



Constraints are some predefined rules applied to columns that are followed to maintain the accuracy and reliability of data. Data Type is self a constraint in PostgreSQL, you can't put string data in the INT data type column. The constraints applied to the columns while creating the table, means constraint create while creating a table. Constraints can be on column level or table level, column-level constraint applied on a particular column and table level constraint applied on the whole table.

Constraints available in PostgreSQL is described below.

1- Primary Key Constraint

We use the primary key constraint on the column to uniquely identify the row in a database table. It can't be null and every value of the column should be unique. The primary key has a crucial role in database schema design, we use them to make relation from one table to another table, and it becomes a foreign key in other tables.

Example of Primary Key Constraint

We will create an Employee table, column "Id" is Primary Key. Use the following query to create an Employee table.


CREATE TABLE public.employee
(
    id serial NOT NULL,
    fname text COLLATE pg_catalog."default",
    lname text COLLATE pg_catalog."default",
    username text COLLATE pg_catalog."default",
    password text COLLATE pg_catalog."default",
    contact text COLLATE pg_catalog."default",
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL,
    managerid integer,
    CONSTRAINT employee_primarykey PRIMARY KEY (id)
)


CONSTRAINT employee_primarykey PRIMARY KEY (id) statement is used to create primary key constraints.
Note: A primary key has one or more columns but a table can have only one primary key. When more than one column is used to create a primary key, it is called a composite key.

2- Unique Key Constraint

The unique key constraint ensures that there should not be the same value for a particular column. It prevents for the duplicate value for a column, it works much more like Primary Key but it can have one null value while Primary Key can't have the null value, for the more update about the difference between Primary key and Unique key the link.

Example of Unique Key Constraint

Suppose we have an employee table and the column UserName should have the unique values, for this the scenario we will use Unique Key constraint for the column UserName.


CREATE TABLE public.employee
(
    id serial NOT NULL,
    fname text COLLATE pg_catalog."default",
    lname text COLLATE pg_catalog."default",
    username text COLLATE pg_catalog."default",
    password text COLLATE pg_catalog."default",
    contact text COLLATE pg_catalog."default",
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL,
    managerid integer,
    CONSTRAINT employee_primarykey PRIMARY KEY (id),
          CONSTRAINT "uk_employee_UserName" UNIQUE (username)
)


CONSTRAINT "uk_employee_UserName" UNIQUE (username) the statement creates the unique key constraint.

3- Not Null Constraint

Not Null Constraint ensures that there should not be the null value for the particular column. If you don't assign Not Null Constraint to the column it by default holds the null value. If you don't want a particular column to have the null value then you can use the Not Null Constraint.

Example of Not Null Constraints

In employee table, if we don't want the column Salary to hold null value then we should use Not Null constraint for this column.


CREATE TABLE public.employee
(
    id serial NOT NULL,
    fname text COLLATE pg_catalog."default",
    lname text COLLATE pg_catalog."default",
    username text COLLATE pg_catalog."default",
    password text COLLATE pg_catalog."default",
    contact text COLLATE pg_catalog."default",
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL,
    managerid integer,
    CONSTRAINT employee_primarykey PRIMARY KEY (id),
    CONSTRAINT "uk_employee_UserName" UNIQUE (username)
)


4- Check Constraint

Sometimes we need to save data with the condition in the table for a particular column, if any condition is applied to the column then the check constants ensure that the particular value being entered into a table column must follow the condition. If the condition is violated by the value being entered in a table, then the record violates the constraint and is not inserted in the table.

Example of Check Constraint

As per the Child labor act of the Government of India, no organization can hire a person below the age of 18 years. So if we are creating an employee table, the Age column should have a condition, the value being inserted must be greater than 18.

CREATE TABLE public.employee
(
    id serial NOT NULL,,
    fname text COLLATE pg_catalog."default",
    lname text COLLATE pg_catalog."default",
    username text COLLATE pg_catalog."default",
    password text COLLATE pg_catalog."default",
    contact text COLLATE pg_catalog."default",
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL,
    managerid bigint,
    age integer,
    CONSTRAINT employee_primarykey PRIMARY KEY (id),
    CONSTRAINT "uk_UserName" UNIQUE (username),
    CONSTRAINT chk_employee_age CHECK (age > 18)
)


5- Foreign Key Constraint

Foreign Key Constraint maintains the referential integrity between two tables. It specifies that the value in a column (or a group of columns) must match the values appearing in some row of another table.

Example of Foreign Key Constraint

Suppose have an employee table and employee's address table, address table has a reference of employee table.

Employee table


CREATE TABLE public.employee
(
    id serial NOT NULL,-- This is primary key
    fname text COLLATE pg_catalog."default",
    lname text COLLATE pg_catalog."default",
    username text COLLATE pg_catalog."default",
    password text COLLATE pg_catalog."default",
    contact text COLLATE pg_catalog."default",
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL,
    managerid integer,
    CONSTRAINT employee_primarykey PRIMARY KEY (id),
          CONSTRAINT "uk_employee_UserName" UNIQUE (username)
)


Address table has reference of Employee table(empid)


CREATE TABLE public.Address
(
          id serial NOT NULL,
          empid integer REFERENCES public.employee (id), --foreign key
          address1 text,
          address2 text,
          city text,
          country text
)


6- Exclusion Constraints

Exclusion Constraint is not the most common type of constants we use it rarely in database table schema, but sometimes it may be important in a particular scenario. It ensures that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null. After reading the definition confused? Let me explain, suppose we design schema for an institute to manage the trainee data such as subject, class time, daily progress report, etc. Now, suppose you are assigning the class time to the student, it may be possible for the same student, the user can assign two classes at the same time. To resolve that types of issue we use Exclusion Constraint.

Example of Exclusion Constraint

I am creating student subject period assigning table(Table name is Period).

CREATE TABLE ClassPeriod
(
          id serial not null,
          stdid integer not null,
          timeperiod tsrange,
          EXCLUDE USING gist (
           stdid WITH =,
           timeperiod WITH &&
          )
);


Now, try to insert some records

INSERT INTO ClassPeriod(stdid,timeperiod)
          VALUES(1,'[2019-01-01 14:30, 2019-01-01 15:30)');
         
INSERT INTO ClassPeriod(stdid,timeperiod)
          VALUES(1,'[2019-01-01 16:30, 2019-01-01 17:30)');
         
INSERT INTO ClassPeriod(stdid,timeperiod)
          VALUES(1,'[2019-01-01 14:30, 2019-01-01 15:30)');


The first and second insert query will run without error because for the same student there are different time period is assigned, while the third insert query will return the error as below.
ERROR:  conflicting key value violates exclusion constraint "classperiod_stdid_timeperiod_excl"
DETAIL:  Key (stdid, timeperiod)=(1, ["2019-01-01 14:30:00","2019-01-01 15:30:00")) conflicts with existing key (stdid, timeperiod)=(1, ["2019-01-01 14:30:00","2019-01-01 15:30:00")).
SQL state: 23P01
The student and Time range of third insert query same as first so it creates the violation exclusion constraint.
Note: Please execute the command CREATE EXTENSION btree_gist, for the database. This query will install the btree_gist extension, which defines the exclusion constraints on plain scalar data types.

PostgreSQL- Common Table Expression


Common Table Expression(CTEs) is a handy feature in PostgreSQL, it is a temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement. It provides a convenient way to manage the completed queries. CTEs are defined within the PostgreSQL statement using the WITH clause. It resolves many difficulties we face in the PostgreSQL query statement such as removes the complexity of sub-query, which executed multiple times, equally helpful in place of temporary tables.


In this article, we will learn the uses of Common Table Expression based on the scenario. The CTEs start with WITH clause and followed by the expression name. Further, we use the expression name in a select query to obtain the records of Common Table Expression.

Syntax of WITH clause in PostgreSQL




WITH CTE_Name [ ( column_name [,...n] ) ]
AS

( CTE_query_definition )


For example, suppose we have the Employee table that contains employee details with managerid.


CREATE TABLE public.employee
(
    id serial NOT NULL,
       fname text COLLATE pg_catalog."default",
    lname text COLLATE pg_catalog."default",
    username text COLLATE pg_catalog."default",
    password text COLLATE pg_catalog."default",
    contact text COLLATE pg_catalog."default",
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL,
    managerid integer,
    CONSTRAINT employee_primarykey PRIMARY KEY (id)

);


 Inserts some records into the employee table, run the following query.


INSERT INTO public.Employee(
  FName, LName, UserName, Password, Contact, Salary, DOJ, ManagerID)
  VALUES ('Name1','last1', 'name1@gmail.com', 'name@0123', '9088877787', '20000','2018/07/07',0);

INSERT INTO public.Employee(
  FName, LName, UserName, Password, Contact, Salary, DOJ, ManagerID)
  VALUES ('Name2','last2', 'name2@gmail.com', 'name@0123', '9288877787', '30000','2018/07/07',3);

INSERT INTO public.Employee(
  FName, LName, UserName, Password, Contact, Salary, DOJ, ManagerID)
  VALUES ('Name3','last3', 'name3@gmail.com', 'name@0123', '9388877787', '40000','2017/09/08',1);

INSERT INTO public.Employee(
  FName, LName, UserName, Password, Contact, Salary, DOJ, ManagerID)
  VALUES ('Name4','last4', 'name4@gmail.com', 'name@0123', '9344877787', '70000','2018/06/09',2);


Common Table Expression without recursive, Suppose I want to get all those records that have Salary greater than 20000.

Simple CTE Example:


WITH CTE(EmpID, FirstName, LastName, MgrID, Salary,YearOfJoining)
AS
(
       SELECT Id,FName,LName,ManagerID,Salary,date_part('year', doj)
              FROM public.Employee
       WHERE Salary>20000
)


SELECT EmpID, FirstName, LastName, MgrID, Salary,YearOfJoining FROM CTE;


Result

Recursive CTE Example

From the Employee table, I want to get the records of the employee and manager, also want to show the level of the hierarchy.


WITH RECURSIVE CTE(EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
       SELECT Id,FName,LName,ManagerID,1 FROM public.Employee  WHERE ManagerID=0
         UNION ALL
       SELECT e.Id, e.FName,e.LName, e.ManagerID, r.EmpLevel + 1
      FROM public.Employee e
         INNER JOIN CTE r
         ON e.ManagerID = r.EmpID
)


SELECT EmpID, FirstName, LastName, MgrID, EmpLevel FROM CTE;


Result

Another example of recursive Common Table Expression


WITH RECURSIVE cte (i)
AS
(
       SELECT 10
         UNION ALL
       SELECT i+10 FROM cte WHERE i+10<= 100
)


SELECT i FROM cte;



Result:

INSERT records using WITH clause

We can modify the records using CTE Statements (Data-Modifying Statements in WITH), PostgreSQL WITH clause allows us to perform several different operations in the same query.

Suppose you want to delete some records from the Employee table and want to log the deleted data in Employee_Log table then use the following query.

WITH deleted_rows
AS
(
    DELETE FROM employee
       WHERE
       username = 'name4@gmail.com'
       RETURNING *
)

INSERT INTO employee_log

SELECT * FROM deleted_rows;

Update records using with clause

Suppose you want to increase the salary of 10% of your employee, for example, see the following query.


WITH upd_emp
AS
(
       UPDATE employee SET Salary = Salary*1.1
       RETURNING *
)


SELECT * FROM upd_emp;




Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...