<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN" "http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd" [
<!ENTITY % CustomDTD SYSTEM "../../../../../../docbook/custom.dtd">
%CustomDTD;
]>
<chapter id="updatable_views">
	<title>Updatable Views</title>
	
	<para>Any view may be marked as updatable.  In many circumstances the view definition may allow the view to be inherently 
	updatable without the need to manually define handing of INSERT/UPDATE/DELETE operations.</para>
	
	<para>An inherently updatable view cannot be defined with a query that has:</para>
	
	<itemizedlist>
		<listitem>
			<para>A set operation (INTERSECT, EXCEPT, UNION).</para>
		</listitem>
		<listitem>
			<para>SELECT DISTINCT</para>
		</listitem>
		<listitem>
			<para>Aggregation (aggregate functions, GROUP BY, HAVING)</para>
		</listitem>
		<listitem>
			<para>A LIMIT clause</para>
		</listitem>
	</itemizedlist>
	
	<para>A UNION ALL can define an inherently updatable view only if each of the UNION branches is itself inherently updatable.  
	A view defined by a UNION ALL can support inherent INSERTs if it is a <xref linkend="partitioned_union"/> and 
	the INSERT specifies values that belong to a single partition.</para>
	
	<para>Any view column that is not mapped directly to a column is not updatable and cannot be targeted by an UPDATE set
	 clause or be an INSERT column.</para>
	 
	<para>If a view is defined by a join query or has a WITH clause it may still be inherently updatable.  However in these situations there
	 are further restrictions and the resulting query plan may execute multiple statements.  
	 For a non-simple query to be updatable, it is required:</para>
	
	<itemizedlist>
		<listitem>
			<para>An INSERT/UPDATE can only modify a single <xref linkend="key_preserved_table"/>.</para>
		</listitem>
		<listitem>
			<para>To allow DELETE operations there must be only a single <xref linkend="key_preserved_table"/>.</para>
		</listitem>
	</itemizedlist>
	
	<para>If the default handling is not available or you wish to have an alternative implementation of an INSERT/UPDATE/DELETE, 
	then you may use <xref linkend="update_procedures"/> to define procedures to handle the respective operations.</para>
	  
	<section id="key_preserved_table">
		<title>Key-preserved Table</title>
		
		<para>A key-preserved table has a primary or unique key that would remain unique if it were projected into the result of the query.  
		Note that it is not actually required for a view to reference the key columns in the SELECT clause.  
		The query engine can detect a key preserved table by analyzing the join structure.  
		The engine will ensure that a join of a key-preserved table must be against one of its foreign keys.</para>		
	</section>
	
</chapter>