Detailed description of using the withcheckoption clause to create a view in oracle

Summary: If the created view contains the where clause, the withcheckoption clause ensures that you can only DML the view within the view conditions. When the created view contains the withcheckoption clause, DML can be normally executed as long as the DML results meet the where clause conditions in the created view.

Summary: If the created view contains the where clause, the with check option clause ensures that you can only DML the view within the view conditions. When the created view contains the with check option clause, DML can work normally only if the DML results meet the where clause conditions in the created view.

Summary:

If the created view contains the where clauseThe with check option clause ensures that you can only DML the view within the view conditions.

When the created view containsWith check option clauseWhen DML results meetTheCreating ViewWhere clause condition, DML can proceed normally.

IfIn subquerySELECTNoIf all the columns in the where condition exist, the primary query statement of the DML type will not be executed successfully.. (?)

If with check option is not added, it will not be checked during insertion.Where condition. That isIf the with check option clause is not used when you use the where clause to create a view, oracle will not check the restrictions of the where clause when you insert data through the view.

Question: The statement for creating a view can contain the group by clause 【Group by clauseCan the having clause or orderby be included? It cannot be a group by clause.

The with check option clause is usedCommon ViewAndInline view).

AboutCommon ViewAndInline ViewFor more information, see:

The relationship between an inline view and a common view and its differences with subqueries

WITH CHECK OPTION

The with check option clause specifies the level of checking to be done when doingDML against the view. if specified, every row that is inserted, updated or deleted through the view must conform to the definition of the view.

The problem:

SQL> CREATE VIEW d20 AS SELECT ename, sal, deptno FROM emp2 WHERE deptno = 20;View created.

SQL> UPDATE d20 SET deptno = 10;3 rows updated.

The solution:

SQL> CREATE VIEW d20 AS SELECT ename, sal, deptno FROM emp2 WHERE deptno = 20  2  WITH CHECK OPTION;View created.

SQL> UPDATE d20 SET deptno = 10;UPDATE d20 SET deptno = 10       *ERROR at line 1:ORA-01402: view WITH CHECK OPTION where-clause violation

The example of applying the with check option clause to a common view is as follows:

If the created view contains the where clauseThe with check option clause ensures that you can only DML the view within the view conditions.
For example, create the following view:
Gyj @ OCM> create or replace view v_t3 as select id, name, salary from t3 where salary >=7000 with check option;


View created.

The condition in the view is salary> = 7000. Therefore, if I insert a row whose salary is smaller than 7000, an error is returned:

Gyj @ OCM> insert into v_t3 values (5, 'gyj5', 1000 );
Insert into v_t3 values (5, 'gyj5', 1000)
*
ERROR at line 1:
ORA-01402: view with check option where-clause violation

If you insert a row larger than 7000, you can insert it successfully:

Gyj @ OCM> insert into v_t3 values (5, 'gyj5', 7500 );

1 row created.

IfIn subquerySELECTNoIf all the columns in the where condition exist, the DML-type primary query statement will not be executed successfully. For example,

1. SQL> INSERT INTO (SELECT order_id, order_date, customer_id FROM ORDERS
2. 2 WHERE order_total = 1000
3. 3 with check option) VALUES (13, SYSDATE, 101 );
4. Insert into (SELECT order_id, order_date, customer_id FROM ORDERS
5. *
6. ERROR at line 1:
7. ORA-01402: view with check option where-clause violation

In the subquerySELECTIf there is no order_total column on the clause, insertion is not allowed.

If with check option is not added, it will not be checked during insertion.Where condition. That isIf the with check option clause is not used when you use the where clause to create a view, oracle will not check the restrictions of the where clause when you insert data through the view.

The above is an example of the insert statement, and the following is an example of the update statement.

Create a view (SQL) showing employees in the sales department ):

1. Enter the following statement:

   Create view sales_employeeAS SELECT emp_id, emp_fname, emp_lname, dept_idFROM employeeWHERE dept_id = 200 WITH CHECK OPTION
   

   The content of this view is as follows:

   SELECT * FROM sales_employee

   Their form in Interactive SQL is as follows:

   Emp_id	Emp_fname	Emp_lname	Dept_id
   129	Philip	Chin	200
   195	Marc	Dill	200
   299	Rollin	Overbey	200
   467	James	Klobucher	200
   ...	...	...	...

2. Transfer Philip Chin to the marketing department, that is
   

   UPDATE sales_employeeSET dept_id = 400 WHERE emp_id = 129

When the UPDATE statement is executed, the following error message is generated:

When the base table 'Employee' is inserted or updated, the with check option violation (ORA-01402?)

I will not try again after deleting the view. In short, when the created view containsWith check option clauseWhen DML results meetTheCreating ViewWhere clause condition, DML can proceed normally.

An example of applying the with check option clause to an in-line view is as follows:

Oracle insert with check option usage

Insert (With check option) values (...)For example:SQL> insert into (select object_id, object_name, object_type from xxx where object_id <1000 WITH CHECK OPTION) 2 values (999, 'testbyhao', 'testtype ');This syntax looks special. It is actually inserted into the table in subquery, but insertion is not allowed if it does not meet the where condition in subquery.If the column to be inserted is not in the where condition of the subquery check, insertion is not allowed.If with check option is not added, it will not be checked during insertion.Where condition.Also, note that subquery is not actually executed.For example:SQL> insert into (select object_id, object_name, object_type from xxx where object_id <1000) 2 values (1001, 'testbyhao', 'testtype'); 1 row created. SQL> insert into (select object_id, object_name, object_type from xxx where object_id <1000 with check option) 2 values (1001, 'testbyhao', 'testtype '); insert into (select object_id, object_name, object_type from xxx where object_id <1000 with check option) * ERROR at line 1: ORA-01402: view with check option where-clause violationThe column inserted here does not contain object_id and cannot be inserted:SQL> insert into (select object_name, object_type from xxx where object_id <1000 with check option) 2 values ('testbyhao', 'testtype'); insert into (select object_name, object_type from xxx where object_id <1000 with check option) * ERROR at line 1: ORA-01402: view with check option where-clause violationWhy is subquery not actually executed? Check the statistics:SQL> set autotrace trace exp statSQL> select object_id, object_name, object_type from xxx where object_id <1000; 955 rows selected.97 consistent getsSQL> insert into (select object_id, object_name, object_type from xxx where object_id <1000) 2 values (999, 'testbyhao', 'testtype'); 1 row created.1 consistent getsIf the with check option clause is not used when you use the where clause to create a view, oracle will not check the restrictions of the where clause when you insert data through the view.See the following for details:Detailed description of using the with check option clause to create a view in oracleThe oracle view is a useful tool for many applications.In the oracle view, for a simple view, oracle allows dml operations. This means that if we create a view on a table, the premise is that there is no limit on its dml operations, I can insert data to the table through this view. For this reason, if we use the where clause to restrict the view creation. When we insert data through a view, will we be subject to the same restrictions of the where clause? The answer is,If the with check option clause is not used when you use the where clause to create a view, oracle will not check the restrictions of the where clause when you insert data through the view. The following is an example:1. Create a tableSQL> create table s_tab (id number );Table created.SQL>2. Create a view (with check option not required)SQL> create or replace view s_view23 select * from s_tab4 where id <5;View created.3. Data insertion TestSQL> insert into s_view values (1 );1 row created.SQL> insert into s_view values (10 );1 row created.SQL> select * from s_view;ID----------1SQL>SQL> select * from s_tab;ID----------110SQL>The result is not checked.4. Create a view (with check option)SQL> create or replace view s_view23 select * from s_tab4 where id <55With check option;View created.5. Insert dataSQL> delete from s_tab;2 row deleted.SQL> insert into s_view values (1 );1 row created.SQL> insert into s_view values (10 );Insert into s_view values (10)*ERROR at line 1:ORA-01402: view with check option where-clause violationSQL> select * from s_view;ID----------1SQL> select * from s_tab;ID----------1