Key-preserved table

A key-preserved table is the most important requirement in order for a join view to be modifiable. 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. 

For example

CREATE VIEW V_RTLR_EMP AS

SELECT C.RTLR_NBR, C.NAME, C.CITY, E.EMP_ID, E.LNAME SALES_REP

FROM RETAILER C, EMPLOYEE E

WHERE C.SALESPERSON_ID = E.EMP_ID;

The view V_RTLR_EMP is a join of RETAILER and EMPLOYEE tables on the RETAILER.SALESPERSON_ID and EMPLOYEE.EMP_ID columns.

We must remember the following important points regarding key-preserved tables:

•  Key-preservation is a property of the table inside the join view, not the table itself independently.
• A table may be key-preserved in one join view, and may not be key-preserved in another join view. 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. In the V_RTLR_EMP view, even though we have included EMP_ID in the SELECT list, the EMPLOYEE table is not key-preserved.
• 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.