How to add unique index to a table?

[aloe]

I created a table in Base. It has only one column. Then I clicked on “Index design” button to create a unique index. I named the index “my_index” and added a column to it (the only one column this table has) and checked the “Unique” checkbox. Upon saving, an error occurrs:

SQL Status: 3409
Error code: -2147217903
Invalid field definition ‘my_index’ in definition of index or relationship.

So, how to add a unique index to a table?

[Edit]

LO version is 4.4.4.3
Also, this database was created as a result of this solution (it’s a connected accdb database)

The exact process of adding index which results in error is this:

1. Create a new table

2. Add column MyID of type Integer [Long]. Available types are:
   

3. Save the table. Base asks for table name and whether to add primary key. Don’t add the primary key, just save. In order to edit the table Base needs either a primary key OR a unique index. The error is for the unique index.

4. Click “Index Design” and “New Index”. Add the index and save.
   

5. Error occurrs. (I forgot to check “Unique” on the screenshots, but the error is regardless of the "Unique setting)
   

[doug]

I get a duplicate index error trying to replicate your problem, involving the primary key, but not the specific error you describe. Does the table already have data in it? Are you using an unsigned integer? I think Base has trouble with unsigned. Are you creating a primary key? Please describe the exact sequence of inputs, as on my setup it repeatedly prompts for saves; do you save? Finally, what version of LO.

[aloe]

The table is empty. I updated my question with more info. There is no unsigned integer, only long integer type (maybe it’s the same). The error does not occurr when I add a primary key at the moment of saving the table (when wizard asks for it). But then the table is not editable (opening it and pressing Alt Ins doesn’t work and menu option Insert->Record is grayed out). So I thought that maybe instead of primary key adding a unique index will help, but can’t add it.

[aloe]

I tried to replicate the error in a brand new database and there was no error. But I need to work with this particular database. As I mentioned in my edited post, it was created as a result of connecting accdb database. So, maybe this is a problem. I noticed for example, that available data types are different in this database and in the new database (e.g. Integer [Long] instead of Integer [INTEGER]).

[doug]

The link you reference offers three different ways to connect. Consider trying the other ways, ADO and ODBC. Your problem rhymes the the one I encountered using a MySQL backend, where Base or connectors failed to recognize the extended range of UNSIGNED integers, meaning the -x to 0 range that should have been added to the normal signed range. If it helps here is that discussion.

[peterwt]

Be interesting to know @aloe if you have solved the problem.

[peterwt]

LO 4.4.1.2 on Windows 8

I have a number of old Access databases and don’t have Access anymore. I have been able to link to these via Base. I have not tried before modifying the database structure so I tried what you described.

Same problem and error when trying to create unique index. If I try this with new Base database no error but can not enter data even though Unique index created (insert record greyed out).

With Access database if I create Primary key it works and I can enter data. Using as you did long integer for MyID. When you create the table you can set MyID as the primary key my right clicking on the green arrow in the column to the left of MyID. Don’t understand why unique index does not work.

[rautamiekka]

Not an answer, not even help to anything on the problem.

[peterwt]

I disagree. I stated that I can create a new table in an Access database connected to Base, create a Primary Key and enter data in the Access database. The difference is that I am using LO 4.4.1.2 whereas @aloe is using LO 4.4.4.3.

[doug]

Continuing troubleshooting steps, if this is possible via any of the ODBC, ADO, or direct, connectors you will be able to do it via the Tools → SQL command in the menu bar in Base. For the HSQLDB internal, the command is:

ALTER TABLE "Table1"
ADD CONSTRAINT "my_key" UNIQUE ("fld1")

I would guess that might work on your MS ACCDB backend, based on what is here and here although I cannot replicate because I also do not have Access.

That said, I suspect that Access would sneak in a primary key when done through ythat system and even though this command will add a UNIQUE constraint successfully, it will not allow you to enter new data through the Base front-end, which peterwt also appears to confirm is a limitation of the Base interface, although a mild one considering that adding a PRIMARY key is easy to do.

[peterwt]

Clarify my post. Access DB unique index same error as @aloe - more on error gives “SQL Status: 3409
Error code: -2147217903”. Base DB can create unique index but cant enter data unless there is also Primary Key and unique index then works. Access DB with Primary key set then add unique index same error - SQL error. Probably as Base SQL different to Access SQL.

[peterwt]

Further investigation shows that most databases require a primary key. This includes Access and Base (dBase is an exception that does not).

[aloe]

The SQL you provided creates a unique index OK. However, as you suspected, it’s not possible to add records, and unfortunately, even if there is primary key, I can’t add records either. This problem exists for all (original and my custom) tables in the accdb connected database. In a native LO database it’s possible to add records of course.