# Databases > Oracle Key Preserved Table

## babi_geek

What is a key preserved table?

----------


## krishnaindia2007

A table is key preserved if every key of the table can also be a key of the result of the join.  In key preserved table rows from the base appears at most once. Key preserved table guarantees to return only one copy of each row from the base table.



```
SQL> CREATE VIEW emp_dept AS 
  2  SELECT      a.empno, a.ename, a.sal , a.deptno, b.dname 
  3  FROM        emp a, dept b 
  4  WHERE       a.deptno = b.deptno ;

View created.

SQL> SELECT * FROM emp_dept;

    EMPNO ENAME            SAL    DEPTNO DNAME
--------- ---------- --------- --------- --------------
     7369 SMITH           5000        20 RESEARCH
     7499 ALLEN         2129.6        30 SALES
     7521 WARD         1663.75        30 SALES
     7566 JONES        3959.73        20 RESEARCH
     7654 MARTIN       1663.75        30 SALES
     7698 BLAKE        3793.35        30 SALES
     7782 CLARK        3260.95        10 ACCOUNTING
     7788 SCOTT           3993        20 RESEARCH
     7839 KING            6655        10 ACCOUNTING
     7844 TURNER        1996.5        30 SALES
     7876 ADAMS         1464.1        20 RESEARCH
     7900 JAMES        1264.45        30 SALES
     7902 FORD            3993        20 RESEARCH
     7934 MILLER        1730.3        10 ACCOUNTING

14 rows selected.
```

In the above example emp is key preserved table. Rows from EMP appears only once. DEPT is not a key preserved table. DEPTNO is key column in dept. But it is not a key column in EMP_DEPT view.

----------


## babi_geek

The rule for updating join views or complex view is
All updatable columns of a join view must map to columns of a key-preserved table. 

This point is o.k.

If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of *repeated tables* are not updatable. 

Here in which sense  repeated tables word is used?

----------


## debasisdas

Key-preserved table 
------------------------------ 
in a join,a table is called a key-preserve table if its keys are preserved through the join.every key of the table can also be a key of the resultant join resultset.every primary key or unique key value in the base table must also be unique in the result set of the join. 
A)key-preservation is a property of the table inside the join view not the table itself independently.a table may be key preseved in one join view and may not be key preserved in another join view. 
B)it is not necessary for the key columns of a table to be selected in the join view for the table to be key preserved. 
C)if the key columns of a table is selected in the view defination ,it does not make the the table key preserved. 
D)the key-preserved property of the table in a join view does not depend on the data inside the table.it depends on the schema design and the relationship between the tables. 
E)a join view may select data from many tables ,any dml operation can modify data from only one underlying table. 
F)user can't refer to the columns of a non-key-preserved table in an insert statment. 
G)delete operation can be performed on a join view if the join view has one and only one key-preserved table.

----------


## krishnaindia2007

*>>Here in which sense repeated tables word is used?*

It refers to non key preserved tables.

----------


## jlloret

Hi,

You say that

F)user can't refer to the columns of a non-key-preserved table in an insert statment. 

But, let us suppose that the table dept is dept(deptno,dname)
and the view is

CREATE VIEW emp_dept AS 
  2  SELECT      a.empno, a.ename, a.sal , b.deptno, b.dname 
  3  FROM        emp a, dept b 
  4  WHERE       a.deptno = b.deptno ;

Then, which would be the problem of

INSERT INTO emp_dept VALUES(50,'Human Resources') ?

For me, this would be equivalent to

INSERT INTO dept VALUES(50,'Human Resources') 

Regards, George

----------


## debasisdas

please go through point E)

----------


## jlloret

Hi,
Thank you for your response, but...

I had proposed to change the definition of the view 

CREATE VIEW emp_dept AS 
2 SELECT a.empno, a.ename, a.sal , *b.deptno*, b.dname 
3 FROM emp a, dept b 
4 WHERE a.deptno = b.deptno ;

So all the columns of my insert are of the same table: department.

INSERT INTO emp_dept(deptno,dname) VALUES(50,'Human Resources') 

The question remains the same:

Why this insert is not equivalent to the following

INSERT INTO dept VALUES(50,'Human Resources')  ?

Regards, George

----------


## amitpatel66

Yes the two inserts that you have mentioned are not the same. If you revisit the definition of the VIEW it says "View is nothing but a VIRTUAL TABLE. And when you create a View by joining more than one table, then the table whose keys are preserved (the column that serves to fetch unique and single record  from a View) is called key Preserved Table. 

In the example of Emp_dept, you can say that the columnd emp_no which is a primary key of the table emp is also a primary key of the view emp_dept becuase it helps you fetch unique and single record for each employee, so emp table is KEY PRESERVED. Now you take dept table, if you use dept_no column, it will give you multiple records because a  single dept can have many employees, so dept table in NOT KEY PRESERVED and You cannot Insert a data through View in to a table which is NOT KEY PRESERVED, rather you will need to insert directly in to a actual table.

I hope this helps!!

----------


## jlloret

Many thanks for your response!
George

----------

