Skip to Main Content
  • Questions
  • are WITH READ ONLY sand WITH CHECK OPTION syntaxes used for views and tables?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kris.

Asked: August 05, 2020 - 5:09 pm UTC

Answered by: Connor McDonald - Last updated: August 06, 2020 - 8:56 am UTC

Category: SQL - Version: Oracle 12c Release 1

Viewed 100+ times

You Asked

Hi,

I am not sure whether or not WITH READ ONLY and WITH CHECK OPTION syntaxes used for views and tables only. I read on Oracle Help Center with the following link https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/CREATE-VIEW.html#GUID-61D2D2B4-DACC-4C7C-89EB-7E50D9594D30
It is said that:
WITH READ ONLY
Specify WITH READ ONLY to indicate that the table or view cannot be updated.
WITH CHECK OPTION
Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.

I hope to receive your response.
Thanks,
Kris.


and we said...

Yes, in my experience exclusively with views.

People often forget that views can be updatable and hence open up the chance of changing the base table, even if that view contains a join.

So most views should probably be created with the READ ONLY option by default, and if they are intended to be updated, then the WITH CHECK ensure people don't update "out of their area" so to speak.


More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.