Single-Table Query Updatability Restrictions

Instead of selecting all fields with the asterisk (*) character, a query can select specific fields. For example:

SELECT 
	Customers.ContactName, 
	Customers.ContactTitle, 
	Customers.Address, 
	Customers.City, 
	Customers.Region, 
	Customers.PostalCode
FROM Customers;

This technique can be useful for hiding certain fields from users, while still allowing them access to the information they need. The fields made available through a query such as this one are fully updatable. It may, however, be impossible to add a new record through this query, either because the fields not included in the output are specified as “required” at the table level, or because the primary key or foreign keys can’t be created with default values.

Another restriction that applies to non-native tables is the requirement of a unique index. For example, Microsoft Jet requires that a primary key be defined for Paradox tables and that ODBC tables have a unique index. Queries based on tables without a unique index aren’t updatable. The exceptions to this are Btrieve and xBase data sources, which don’t require a unique index. For SQL Server, a table may be updatable if it has a timestamp field, even if it doesn’t have a unique index.