Key Preserved Table

What is a key preserved table?

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.

[CODE]
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.

[/CODE]

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.

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 [B]repeated tables [/B]are not updatable.

Here in which sense repeated tables word is used?

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.

[B]>>Here in which sense repeated tables word is used?[/B]

It refers to non key preserved tables.

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

please go through point E)

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]b.deptno[/B], 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

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!!

Many thanks for your response!
George