Oracle insert with check option usage

Insert into (<select clause> 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 very special. It is actually inserted into the table in subquery, but it is not allowed to be inserted 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.

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_typeFrom 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 Violation

The 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 Violation

Why 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 gets