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 ?

asked 12 Oct '17, 05:51

yan's gravatar image

yan
806712
accept rate: 0%

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.

(12 Oct '17, 11:21) Chris Keating

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;

permanent link

answered 12 Oct '17, 07:12

yan's gravatar image

yan
806712
accept rate: 0%

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

(12 Oct '17, 08:03) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×79
×72

question asked: 12 Oct '17, 05:51

question was seen: 1,573 times

last updated: 12 Oct '17, 11:21