Null Values in Underlying Objects

The third restriction applies to INSERT statements if there are some NOT NULL columns in the tables from which the view is derived.

For example, suppose null values are not allowed in a column of a table underlying a view. Normally, when you insert new rows through a view, any columns in underlying tables that don't have defaults and that are not included in the view are given null values. If null values are not allowed in one or more of these columns and they do not have defaults, no inserts are allowed through the view.

For example:

CREATE VIEW titlesview
AS SELECT title_id, price, ytd_sales
FROM titles
WHERE type = 'business'

You'll receive a message stating that the command did not return any data, and it did not return any rows. This is fine. The view has been created.

Null values are not allowed in the title column of the underlying table titles, so no INSERT statements can be allowed through titlesview. Although the title column doesn't even exist in the view, its prohibition of null values makes any inserts into the view illegal.

Similarly, if the title_id column has a unique index, updates or inserts that would duplicate any values in the underlying table are rejected, even if the entry doesn't duplicate any value in the view.