Views that include IDENTITY columns

You can define a view that includes an IDENTITY column by listing the column name, or the syb_identity keyword, in the view’s select statement. For example:

create view sales_view
as select syb_identity, stor_id
from sales_daily

However, you cannot add a new IDENTITY column to a view by using the identity_column_name = identity(precision) syntax.

You can select the IDENTITY column from the view using the syb_identity keyword, unless the view:

• Selects the IDENTITY column more than once

• Computes a new column from the IDENTITY column

• Includes an aggregate function

• Joins columns from multiple tables

• Includes the IDENTITY column as part of an expression

If any of these conditions is true, Adaptive Server does not recognize the column as an IDENTITY column with respect to the view. When you execute sp_help on the view, the column displays an “Identity” value of 0.

In the following example, the row_id column is not recognized as an IDENTITY column with respect to the store_discounts view because store_discounts joins columns from two tables:

create view store_discounts
as
select stor_name, discount
from stores, new_discounts
where stores.stor_id = new_discounts.stor_id

When you define the view, the underlying column retains the IDENTITY property. When you update a row through the view, you cannot specify a new value for the IDENTITY column. When you insert a row through the view, Adaptive Server generates a new, sequential value for the IDENTITY column. Only the table owner, Database Owner, or System Administrator can explicitly insert a value into the IDENTITY column after setting identity_insert on for the column’s base table.

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF