A join view is a view which is based on more than one base table. Any insert, update or delete statement on a view can update only one of the base tables involved in the view. A table is called a key preserved in a join view, if the primary and unique keys are unique on the views result set.
Sql> create view t1_view as select a.empno, a.ename, a.deptno, b.dname
2 from emp a, dept b
3 where a.deptno = b.deptno;
View created.
Sql>desc t1_view;
| Name | Null? | Type |
|---|---|---|
| EMPNO | NOT NULL | NUMBER(4) |
| ENAME | VARCHAR2(10) | |
| DEPTPNO | NUMBER(2) | |
| DNAME | VARCHAR2(14) |
Sql> select * from t1_view;
| EMPNO | ENAME | DEPTNO | DNAME |
|---|---|---|---|
| 7369 | SMITH | 20 | RESEARCH |
| 7499 | ALLEN | 30 | SALES |
| 7521 | WARD | 30 | SALES |
| 7566 | JONES | 20 | RESEARCH |
| 7654 | MARTIN | 30 | SALES |
5 rows selected.
In the above view table emp is key preserved because primary key of the emp is also unique in the view. The dept table is not key preserved as it primary key deptno is duplicated many times.
You can update only the key preserved table through the view.
If the view is defined with the WITH CHECK OPTION then you cannot update columns that join the base tables.
Insert statements also cannot refer to any column of the non key preserved table. If a view is created with “WITH CHECK OPTION” , then no insert is permitted on view.
No comments:
Post a Comment