DROP INDEX after a CREATE TABLE leads to error ?

[Former Member]

Dear SQLAnywhere community,

When we execute

CREATE TABLE TABLE_1 (
  ID INTEGER NOT NULL,
  CODE VARCHAR(32) NOT NULL,
  PRIMARY KEY(ID ASC));

followed by a DROP INDEX XYZ-ID-IDX;

We get an "Invalid reference to or operation on constraint 'primary'" error.

Taking into account the fact that the index is done on another table (let say TABLE_2) that has no link with TABLE_1, have any of you got any idea why this comes ?

[chris_keating]

This appears to be incorrect behavior. The error indicates that there was an operation that affected the primary key that was not permitted. UltraLite uses "primary" as the name for primary key and primary key constraints -> if a different name is used, UltraLite will silently rename it to "primary". An ALTER to rename the primary key should fail with the error you reported as it is not permitted. I cannot explain why UltraLite is attempting an operation on "primary" based on the DROP INDEX statement.

If you execute:
select st.table_name, si.index_name, si.type from sysindex si join systable st on si.table_id = st.object_id

and locate instances of the index "XYZ-ID-IDX", what is the value of the type column for the tables that you seen this issue?

Is it possible to get access to a database that has this issue for investigation? If you have a support plan, you can open a new message or alternatively, I can arrange a location to upload the file.

[Former Member]

Hello everyone again, a colleague has found the solution. One has to put fully qualified names in this specific case.

Solution : DROP INDEX TABLE_2.XYZ-ID-IDX;

[VolkerBarth]

Yes, index names need not be unique, so it seems you have tried to drop a possibly automatically created PK index...