PostgreSQL – Creating Updatable Views Using WITH CHECK OPTION Clause

PostgreSQL is the most advanced general purpose open source database in the world. pgAdmin is the most popular management tool or development platform for PostgreSQL. It is also an open source development platform. It can be used in any Operating Systems and can be run either as a desktop application or as a web in your browser. 

In this article, we are going to discuss :

• Configure pgAdmin with your OS.
• Creating updatable views using the WITH CHECK OPTION clause.

Configure pgAdmin :

The pgAdmin can be downloaded from here. The latest version is pgAdmin 4 v5.2.

1. Download the PostgreSQL installer from here. Choose the proper version and Windows bits of your PC.

2. After downloading, the Setup Wizard will pop up. Specify the directory. You can keep everything as default and click Next.

3. Now comes the most important part. It will ask to enter a Password. The password is really important, please keep a note of it.

4. Now select the Port Number. By default it is “5432”.

5.Click Next and then install the setup. No need to add the Stack Builder, uncheck it and click on Finish.

6. Now install pgAdmin from the link provided above. 

7. After installation finishes, restart your computer to ensure pgAdmin is working.

8. Open the pgAdmin from the Start window and then it will ask for a password. You should use the same password as discussed in Step 3.

9. Now click on Server and you can easily create a database and write a query in the Query Tool.

For creating database and tables and for more information about Query Tool in pgAdmin visit CREATE and Recursive Query.

Sample Input : 

Consider the below ER Model below to create Employee details and Department details tables for a company.

ER-Diagram of Sample Tables

Creating updatable views using the WITH CHECK OPTION clause :

View is generally created when we want to show a subset of a table by the selection of certain rows or columns. It is used to restrict access to the original table and users can see the sub portion of the base table. For more details about views and its types visit PostgreSQL – Create updatable Views.

Let’s summarize about creating updatable views as discussed in PostgreSQL – Create updatable Views and the problems faced :

Inputs : 

1. Department Details

Department Table

2. Employee Details

Employee Table

Now, let’s create an updatable view for the Employee Table. Let’s make a view for all the employees who are working in the “HR Department” of the company.

CREATE VIEW employees_hr
AS SELECT employee_id,employee_name,department_id
FROM Employee
WHERE department_id=100

HR VIEW 

Since it is an updatable view, we can insert values and it will reflect back in the table Employee. If the “Department_ID” of the newly inserted value is equal to 100, then it will be added in both the Employee Table and Employees_HR view table else it won’t be added in this view HR because the WHERE condition becomes FALSE.

New Data insertion in Updatable View

New Employee Table

Now, we can also remove a view and make it invisible by making an UPDATE query which is shown below. Suppose we remove Neha from the “HR view” and set to some other department and also change her name to “Nehaa”.

UPDATE employees_hr 
SET
    employee_name='Nehaa',
    department_id=104
WHERE
employee_id=1004

/* Removing view Neha from HR by updating her name and department */

Neha is Invisible now in this view

Nehaa is Updated in Employee Table

This is the problem in an updatable view without WITH CHECK OPTION. The user who can access the “view HR” can easily modify the data and a change in it will reflect back into the parent table. This is not a good sign for any organization because they are giving access to the users to change the data. People can put wrong data and also change the designation of an employee.  Hence, the data is no longer safe in the database.

So, to overcome this we can use the WITH CHECK OPTION clause. Let’s make another view for the employees who are working in the Sales Department having Department ID = 101.

CREATE VIEW employees_Sales AS
SELECT * FROM Employee
WHERE department_id=101 WITH CHECK OPTION

View of Sales Department

Now, let’s try to insert details of an employee who is not from the Sales Department and also try to UPDATE a record from this view.

INSERT

UPDATE

Hence, the consistency is maintained and the WITH CHECK option is prohibiting the user not to INSERT or UPDATE any data which is not relevant to the view created.