The concept of a key-preserved table is fundamental to understanding the
restrictions on modifying join views. A table is key preserved if every key of
the table can also be a key of the result of the join. So, a key-preserved
table has its keys preserved through a join.
Note: It is not necessary that the key or keys of a table be selected for it
to be key preserved. It is sufficient that if the key or keys were selected,
then they would also be key(s) of the result of the join.
Attention: The key-preserving property of a table does not depend on the
actual data in the table. It is, rather, a property of its schema and not of
the data in the table. For example, if in the EMP table there was at most one
employee in each department, then DEPT.DEPTNO would be unique in the result of
a join of EMP and DEPT, but DEPT would still not be a key-preserved table.
If you SELECT all rows from EMP_DEPT view, the results are:
SELECT * FROM EMP_DEPT;
EMPNO ENAME SAL DEPTNO DNAME LOC
----- ------ ---- ------ ---------- --------
7369 SMITH 800 20 RESEARCH DALLAS
7499 ALLEN 1600 30 SALES CHICAGO
7521 WARD 1250 30 SALES CHICAGO
7566 JONES 2975 20 RESEARCH DALLAS
7654 MARTIN 1250 30 SALES CHICAGO
7698 BLAKE 2850 30 SALES CHICAGO
7782 CLARK 2695 10 ACCOUNTING NEW YORK
7788 SCOTT 3000 20 RESEARCH DALLAS
7839 KING 5500 10 ACCOUNTING NEW YORK
7844 TURNER 1500 30 SALES CHICAGO
7876 ADAMS 1100 20 RESEARCH DALLAS
7900 JAMES 950 30 SALES CHICAGO
7902 FORD 3000 20 RESEARCH DALLAS
7934 MILLER 1430 10 ACCOUNTING NEW YORK
14 rows selected.
In this view, EMP is a key-preserved table, because EMPNO is a key of the EMP
table, and also a key of the result of the join. DEPT is not a key-preserved
table, because although DEPTNO is a key of the DEPT table, it is not a key of
the join.
restrictions on modifying join views. A table is key preserved if every key of
the table can also be a key of the result of the join. So, a key-preserved
table has its keys preserved through a join.
Note: It is not necessary that the key or keys of a table be selected for it
to be key preserved. It is sufficient that if the key or keys were selected,
then they would also be key(s) of the result of the join.
Attention: The key-preserving property of a table does not depend on the
actual data in the table. It is, rather, a property of its schema and not of
the data in the table. For example, if in the EMP table there was at most one
employee in each department, then DEPT.DEPTNO would be unique in the result of
a join of EMP and DEPT, but DEPT would still not be a key-preserved table.
If you SELECT all rows from EMP_DEPT view, the results are:
SELECT * FROM EMP_DEPT;
EMPNO ENAME SAL DEPTNO DNAME LOC
----- ------ ---- ------ ---------- --------
7369 SMITH 800 20 RESEARCH DALLAS
7499 ALLEN 1600 30 SALES CHICAGO
7521 WARD 1250 30 SALES CHICAGO
7566 JONES 2975 20 RESEARCH DALLAS
7654 MARTIN 1250 30 SALES CHICAGO
7698 BLAKE 2850 30 SALES CHICAGO
7782 CLARK 2695 10 ACCOUNTING NEW YORK
7788 SCOTT 3000 20 RESEARCH DALLAS
7839 KING 5500 10 ACCOUNTING NEW YORK
7844 TURNER 1500 30 SALES CHICAGO
7876 ADAMS 1100 20 RESEARCH DALLAS
7900 JAMES 950 30 SALES CHICAGO
7902 FORD 3000 20 RESEARCH DALLAS
7934 MILLER 1430 10 ACCOUNTING NEW YORK
14 rows selected.
In this view, EMP is a key-preserved table, because EMPNO is a key of the EMP
table, and also a key of the result of the join. DEPT is not a key-preserved
table, because although DEPTNO is a key of the DEPT table, it is not a key of
the join.