Student table:

95001 Li Yongnan 20 CS

95002 Liu Chen female 21 is

95003 Wang minnv 18 Ma

95004 tension male 19 is

Establish view is_ Student displays the student number, name and gender of all students in “is” department.

create view IS_ STUDENT

as

select Sno,Sname,Ssex from Student

where Sdept=’IS’

with check option;

insert tuples (‘95009 ‘,’wangwu’,’male ‘) into the view to view the data values inserted in the student table of the basic table.

Here is the failed message: Message 550, level 16, status 1, line 1

The attempted insert or update has failed because the target view or a view crossed by the target view specifies with check option, and one or more result rows of the operation do not conform to the check option constraint. Statement terminated

Answer:

With check option can be explained as follows: when you modify a view, you must also be able to see the modified result through the view.

For example, if you insert, the added record must be visible after refreshing the view; if you modify it, the modified result must also be visible through the view; if you delete it, of course, only the records displayed in the view can be deleted.

Summary:

First of all, the view only operates on the data that it can query. For the data that it can’t query, even if there is a base table, it can’t operate through the view.

1. For update, there is a with check option to ensure that the data will be queried by the view after update

2. For delete, it is the same whether there is with check option or not

3. For insert, there is a with check option to ensure that the data will be queried by the view after insert

4. For views without where clause, it is unnecessary to use with check option

Original address: http://www.cnblogs.com/wangaohui/archive/2012/12/15/2819419.html