What are two advantages of Key Preserved Table in Join ViewS

What are two advantages of Key Preserved Table in Join View?

In simple terms, a table is key preserved if the table key participates in the view as a key. In short, a key-preserved table has its key columns preserved through a SQL join.

For a simple example of a key preserved table, consider the one-to-many relationship between a CUSTOMER and an ORDERS table.

A view could be created CUSTOMER_ORDERS that displays all orders for the customer, using CUST_ID as a primary key and using CUST_ID as the key to find the ODERS for a customer. In this example, the CUSTOMER table is said to be key preserved because the result of the join has a unique key CUST_ID.

If you want key preserved tables in a view, you must explicitly define the primary and foreign keys in the tables, or define unique indexes. The docs note that if you want a view to be inherently updatable, it must have special treatment for key preserved tables.

For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view has the CHECK OPTION, join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE.

For an INSERT statement, all columns into which values are inserted must come from a key-preserved table, and the view must not have the CHECK_OPTION.

Advantages:

accepted

Key preserved means that 1 key value goes to 1 table. Giving counter examples may help you understand this concept better.

Example1:

Your view contains aggregation. Suppose you have following view structure.

GroupID, AverageSalary

1 , 50000

2, 166000

3, 155000

In this example: your values comes from more than one rows. If you try to update AverageSalary in this view, database has no way to find WHICH rows to update.

Example2: Your view shows values from more than one table. Your view shows values from PERSON and PERSON_CONTACT_DETAILS(ID,PersonID,ContactType,ContactValue) table.

Example rows :

1,1,email,ddd@example.com

1,1,phone,898-98-99

You join this 2 table and show more business friendly information in view.

PersonId,Name,LastName, Phone1,Email1

Here you would like to update Phone1 and Email1. But your personID maps to two different rows, may be more rows, in this example. In this view, again, database has no way to find WHICH rows to update.

Note: If you restrict your view sql and makes it clear to find which rows to update it may work.

This two example is first examples which comes to my mind. They can be increased. But concept is clear. Database needs to map 1 key value to 1 table. For example you have one to one PERSON, PERSON_DETAILS tables. Here view and update will work since it is one to one.