A join view is a view based on a join. Special considerations apply when you issue a DML(INSERT, UPDATE, or DELETE) statement against a join view. Ever thought about what happens when you insert a row into a join view—which table does the row go into? And what happens when you delete a row from a join view—which table does it gets deleted from?
To be modifiable, a join view must not contain any of the following :-
• Hierarchical query clauses, such as START WITH or CONNECT BY.
• GROUP BY or HAVING clauses.
• Aggregate functions, such as AVG, COUNT, MAX, MIN, SUM, and so forth.
• Set operations, such as UNION, UNION ALL, INTERSECT, MINUS.
• The DISTINCT operator.
• The ROWNUM pseudo column.
A DML statement on a join view can modify only one base table (key preserved table) of the view.
In a join, a table is called a KEY-PRESERVED TABLE if its keys are preserved through the join — every key of the table can also be a key of the resultant join result set. Every primary key or unique key value in the base table must also be unique in the result set of the join.
Important Rules For Key-Preserved Tables :-
• A table may be key-preserved in one join view, and may not be key-preserved in another join view. The key-preserved property of a table in a join view doesn’t depend on the data inside the table. It depends on the schema design and the relationship between the tables.
• It is not necessary for the key column(s) of a table to be Selected in the join view for the table to be key-preserved.
• On the other hand, if we select the key column(s) of a table in the view definition, it doesn’t make that table key-preserved automatically.
SQL> create table dept(
2 deptno number(2,0),
3 dname varchar2(14),
4 loc varchar2(13),
5 constraint pk_dept primary key (deptno)
6 );
Table created.
SQL> create table emp(
2 empno number(4,0),
3 ename varchar2(10),
4 job varchar2(9),
5 mgr number(4,0),
6 hiredate date,
7 sal number(7,2),
8 comm number(7,2),
9 deptno number(2,0),
10 constraint pk_emp primary key (empno),
11 constraint fk_deptno foreign key (deptno) references dept (deptno)
12 );
Table created.
SQL> CREATE VIEW emp_dept_view
2 AS
3 SELECT e.empno,e.ename,e.sal,d.deptno,d.loc,d.dname
4 FROM emp e,dept d
5 WHERE e.deptno = d.deptno;
View created.
SQL> SELECT * FROM emp_dept_view;
EMPNO ENAME SAL DEPTNO LOC DNAME
---------- ---------- ---------- ---------- ------------- --------------
7839 KING 5000 10 NEW YORK ACCOUNTING
7698 BLAKE 2850 30 CHICAGO SALES
7782 CLARK 2450 10 NEW YORK ACCOUNTING
7566 JONES 2975 20 DALLAS RESEARCH
7788 SCOTT 3000 20 DALLAS RESEARCH
7902 FORD 3000 20 DALLAS RESEARCH
7369 SMITH 800 20 DALLAS RESEARCH
7499 ALLEN 1600 30 CHICAGO SALES
7521 WARD 1250 30 CHICAGO SALES
7654 MARTIN 1250 30 CHICAGO SALES
7844 TURNER 1500 30 CHICAGO SALES
7876 ADAMS 1100 20 DALLAS RESEARCH
7900 JAMES 950 30 CHICAGO SALES
7934 MILLER 1300 10 NEW YORK ACCOUNTING
14 rows selected.
--Here as per the view data,EMP table is KEY PRESERVED TABLE & DEPT table is NON KEY PRESERVED TABLE.
SQL> SELECT column_name,updatable,insertable,deletable FROM user_updatable_columns WHERE table_name = 'EMP_DEPT_VIEW';
COLUMN_NAME UPD INS DEL
------------- --- --- ---
EMPNO YES YES YES
ENAME YES YES YES
SAL YES YES YES
DEPTNO NO NO NO
LOC NO NO NO
DNAME NO NO NO
6 rows selected.
--An INSERT operation can be performed on a join view if it attempts to insert a row in the key-preserved table.
SQL> CREATE VIEW emp_dept_view
2 AS
3 SELECT e.empno,e.ename,e.sal,d.deptno,d.loc,d.dname
4 FROM emp e,dept d
5 WHERE e.deptno = d.deptno;
View created.
SQL> INSERT INTO emp_dept_view (empno,ename,sal) VALUES(1111,'TESTNAME1',1000);
1 row created.
SQL> INSERT INTO emp_dept_view (deptno,dname,loc) VALUES(50,'ENGINEERING','INDIA');
INSERT INTO emp_dept_view (deptno,dname,loc) VALUES(50,'ENGINEERING','INDIA')
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> INSERT INTO emp_dept_view (empno,ename,sal,deptno,dname,loc) VALUES(2222,'TESTNAME2',2000,60,'FINANCE','PAKISTAN');
INSERT INTO emp_dept_view (empno,ename,sal,deptno,dname,loc) VALUES(2222,'TESTNAME2',2000,60,'FINANCE','PAKISTAN')
*
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view
SQL> CREATE OR REPLACE VIEW emp_dept_view
2 AS
3 SELECT e.empno,e.ename,e.sal,d.deptno,d.loc,d.dname
4 FROM emp e,dept d
5 WHERE e.deptno = d.deptno
6 WITH CHECK OPTION;
View created.
SQL> SELECT constraint_name FROM user_constraints WHERE table_name = 'EMP_DEPT_VIEW';
CONSTRAINT_NAME
-------------------
SYS_C009022
SQL> INSERT INTO emp_dept_view (empno,ename,sal) VALUES(1111,'TESTNAME1',1000);
INSERT INTO emp_dept_view (empno,ename,sal) VALUES(1111,'TESTNAME1',1000)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
SQL> INSERT INTO emp_dept_view (deptno,dname,loc) VALUES(50,'ENGINEERING','INDIA');
INSERT INTO emp_dept_view (deptno,dname,loc) VALUES(50,'ENGINEERING','INDIA')
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> INSERT INTO emp_dept_view (empno,ename,sal,deptno,dname,loc) VALUES(2222,'TESTNAME2',2000,60,'FINANCE','PAKISTAN');
INSERT INTO emp_dept_view (empno,ename,sal,deptno,dname,loc) VALUES(2222,'TESTNAME2',2000,60,'FINANCE','PAKISTAN')
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
--DELETE operations can be performed on a join view if the join view has only one key-preserved table. The view EMP_DEPT_VIEW has only one key-preserved table (EMP) therefore, you can delete from this join view as in the following example.
SQL> CREATE OR REPLACE VIEW emp_dept_view
2 AS
3 SELECT e.empno,e.ename,e.sal,d.deptno,d.loc,d.dname
4 FROM emp e,dept d
5 WHERE e.deptno = d.deptno;
View created.
SQL> DELETE FROM emp_dept_view WHERE dname = 'ACCOUNTING';
3 rows deleted.
SQL> DELETE FROM emp_dept_view WHERE ename = 'BLAKE';
1 row deleted.
SQL> CREATE OR REPLACE VIEW emp_dept_view
2 AS
3 SELECT e.empno,e.ename,e.sal,d.deptno,d.loc,d.dname
4 FROM emp e,dept d
5 WHERE e.deptno = d.deptno
6 WITH CHECK OPTION;
View created.
SQL> DELETE FROM emp_dept_view WHERE dname = 'ACCOUNTING';
3 rows deleted.
SQL> DELETE FROM emp_dept_view WHERE ename = 'BLAKE';
1 row deleted.
--An UPDATE operation can be performed on a join view if it attempts to update a column in the key-preserved table.
SQL> CREATE OR REPLACE VIEW emp_dept_view
2 AS
3 SELECT e.empno,e.ename,e.sal,d.deptno,d.loc,d.dname
4 FROM emp e,dept d
5 WHERE e.deptno = d.deptno;
View created.
SQL> UPDATE emp_dept_view SET sal = sal + 1 WHERE dname = 'ACCOUNTING';
3 rows updated.
SQL> UPDATE emp_dept_view SET dname = 'XYZ' WHERE loc = 'BOSTON';
UPDATE emp_dept_view SET dname = 'XYZ' WHERE loc = 'BOSTON'
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> CREATE OR REPLACE VIEW emp_dept_view
2 AS
3 SELECT e.empno,e.ename,e.sal,d.deptno,d.loc,d.dname
4 FROM emp e,dept d
5 WHERE e.deptno = d.deptno
6 WITH CHECK OPTION;
View created.
SQL> UPDATE emp_dept_view SET sal = sal + 1 WHERE dname = 'ACCOUNTING';
3 rows updated.
SQL> UPDATE emp_dept_view SET dname = 'XYZ' WHERE loc = 'BOSTON';
UPDATE emp_dept_view SET dname = 'XYZ' WHERE loc = 'BOSTON'
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
