Troubleshooting and Error Messages Guide

Home

Troubleshooting and Error Messages Guide

Chapter 3 Error Message Writeups

Query Processor Errors

Error 311

Severity

Error message text

The optimizer could not find a unique index which it could use to scan table '%.*s' for cursor '%.*s'.

Explanation

For an explicit updatable cursor scan, Adaptive Server requires that a unique index exist on the table. A unique index ensures that the cursor will be positioned at the correct row the next time a fetch is performed on that cursor. Error 311 occurs when a unique index does not exist during a scan required for a cursor marked for update.

Action

Create a unique index using one of the following methods:

Drop your existing index. Create an index with the unique option or use alter table add constraint with the unique option.
Create a new index using create index with the unique option or use alter table add constraint with the unique option.
When you create an index with the unique option, if there is a duplicate key value or if more than one row contains a null value, the command is aborted and Error 1505 is raised, showing the duplicate value. Refer to Error 1505 if that happens.
Add an IDENTITY column using alter table and the identity option.

If your table has one or more IDENTITY columns, you can, for future indexes you create for this table, use the database option identity in nonunique index. When this option is set, any nonunique index created on a table with an IDENTITY column will have the IDENTITY column automatically included as the last key field of the index. This allows an otherwise nonunique index to be used for a cursor marked for update.

However, all indexes would then be considered unique by the optimizer since every index that is created would be unique and this could result in poor performance for some queries. Since the identity in nonunique index option does not affect existing indexes, only future indexes you create, the existing 311 error will not be solved.

Warning!

Do not use the identity in nonunique index option unless you plan to test your queries.

To use the identity in nonunique index option, the table must already have an IDENTITY column, either from a create table statement or by setting the auto identity database option to true before creating the table.

The commands to set this option are:

1> use master
2> go

1> sp_dboption database_name,
2> "identity in nonunique index", true
3> go

1> use database_name
2> go

1> checkpoint
2> go

Additional information

Refer to the ASE Reference Manual for information about create index, alter table, and sp_dboption.

Versions in which this error is raised

All versions


	Expand Search