The CHECK clause for updatable views

Written by Giuseppe Broccolo 

Since PostgreSQL 9.3, it is possible to update and insert into views directly, so long as the view refers to only one underlying table.

PostgreSQL 9.4 allows us to use the CHECK clause for INSERTs into updatable views. For example, consider a table composed of just one integer column; and consider two views, one on numbers divisible by 2 and one on numbers divisible by 3. If we try to insert the number 123 into the first view:

—-

$ CREATE TABLE some_data(id int4 PRIMARY KEY);

CREATE TABLE

$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id%2;

CREATE VIEW

$ CREATE VIEW second AS SELECT * FROM some_data WHERE 0 = id%3;

CREATE VIEW

$ INSERT INTO first(id) VALUES (123);

—-

It will be inserted into the underlying table, even though the view is only for numbers divisible by 2 (so the new value will not be visible in the view). In PostgreSQL 9.4 the CHECK clause has been introduced to properly manage INSERTs into views by checking in advance that values are compatible with the definition of the view.

There are two possible options:

* CASCADED CHECK – this is the default option, where checks cascade to other views defined on the same underlying table

* LOCAL CHECK – only the view that is the target of an INSERT is checked

Here it is shown how to use the CHECK clause in the above example:

—-

$ DROP VIEW first;

DROP VIEW

$ DROP VIEW second;

DROP VIEW

$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id % 2 WITH CHECK OPTION;

CREATE VIEW

$ CREATE VIEW second AS SELECT * FROM some_data WHERE 0 = id % 3 WITH CHECK OPTION;

CREATE VIEW

$ CREATE VIEW third AS SELECT * FROM first WHERE 0 = id % 3 WITH CHECK OPTION;

CREATE VIEW

$ INSERT INTO first(id) VALUES (14);

INSERT 0 1

$ INSERT INTO first(id) VALUES (15);

ERROR:  new row violates WITH CHECK OPTION for view “first”

$ INSERT INTO second(id) VALUES (15);

INSERT 0 1

$ INSERT INTO third(id) VALUES (6);

INSERT 0 1

$ INSERT INTO third(id) VALUES (15);

ERROR:  new row violates WITH CHECK OPTION for view “first”

Note that the view “third” is defined on the view “first”.

The value ’14’ is correctly inserted in the first view, while the value ’15’ can be inserted only into second, not first – as expected. We can insert ‘6’ into the third view because it is divisible by both 3 and 2. The error about inserting ’15’ into the third view even though it is divisible by 3 is because it violates the divisible-by-2 CHECK clause on the parent view, first.  In this case, it is not sufficient to use a LOCAL CHECK clause in both views to work around the problem:

—-

$ DROP VIEW first;

DROP VIEW

$ DROP VIEW third;

DROP VIEW

$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id % 2 WITH LOCAL CHECK OPTION;

CREATE VIEW

$ CREATE VIEW third AS SELECT * FROM first WHERE 0 = id % 3 WITH LOCAL CHECK OPTION;

CREATE VIEW

$ INSERT INTO third(id) VALUES (15);

ERROR:  new row violates WITH CHECK OPTION for view “first”

—-

The working example is shown here:

—-

$ DROP VIEW first;

DROP VIEW

$ DROP VIEW third;

DROP VIEW

$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id % 2;

CREATE VIEW

$ CREATE VIEW third AS SELECT * FROM first WHERE 0 = id % 3 WITH LOCAL CHECK OPTION;

CREATE VIEW

$ INSERT INTO third(id) VALUES (15);

INSERT 0 1

—-

Conclusions

This new check mechanism can be applied directly on updatable views during the INSERT phase. It reinforces more and more the role of the database in maintaining data integrity.