Subscribe to this thread
Home - General / All posts - Can't add index
Forest
625 post(s)
#22-Sep-17 00:18

In 9.0.163.4, I have been trying to import a table of points (again) and this time, I can't make the table writable by creating an index, so I can't create a geom field and compose points. I have done this before so many times now, the process is ingrained.

To produce the issue, use the 32 bit version to import the tables from an accdb file. Data comes in fine by tables are grey which means read only. An index is required to make the tables writable so that I can create points in a geom field. I can't make the index and do not know why. See attached jpg.

I was trying to use the primary key field from the ms-access side for indexing on the manifold side. The data is fine and can be imported and mapped in M8.

Attachments:
CantAddIndex.jpg

tjhb
10,094 post(s)
#22-Sep-17 00:36

You might be confusing writable and editable.

A table with no BTREE index is not editable (and records can't be selected). That's because records can't be singled out in any way.

But the table may still be writable (unless it is read-only). That's a different thing. In particular, you can still add fields (including a Geom field), add indexes,* and add records, and you can update a field (for all records) using SQL.

(*This has to be the case. If you couldn't add a BTREE index, you couldn't make the table editable.)

Grey does not mean read-only. Grey means you can't select or edit individual records. Subtly different perhaps, but completely different.

I suspect that the reason you can't add a BTREE index on the PT_ID field is that values in that field are not unique. (As unlikely as that sounds.)

You can test that with SQL:

SELECT [PT_ID] FROM [Table]

GROUP BY [PT_ID]

HAVING COUNT([PT_ID]) > 1

;

(No BTREE index is required to run the query.)

Even if you can't add a BTREE on PT_ID, you can still add a Geom field and populate it (e.g. from Longitude and Latitude fields).

adamw


10,447 post(s)
#22-Sep-17 07:45

A correction: grey means you cannot edit. You might still be able to select - that's the case of a table having an unique index but being read-only. Or it is the reverse and the table can be read-write and not have an unique index - cannot edit this either. Or it is both and the table is read-only and does not have an unique index - cannot edit.

adamw


10,447 post(s)
#22-Sep-17 07:49

To add to what Tim says regarding PT_ID possibly not being unique, it might also contain NULLs. A BTREE cannot handle NULLs, there are different indexes for that. A common case of NULLs is unfinished new records committed by mistake.

Here is how to recover: add a new INT64 field named MFD_ID and add a new BTREE index on that field named MFD_ID_X. This will use the internal field and index available for any table in a MAP file. After that, if you want, you can edit the duplicate values / NULLs in the PT_ID field, then build an index on that field and delete the MFD_ID / MFD_ID_X.

If you upload the MAP file we will be able to tell what specifically is going on. (I realize you had an unique index in ACCDB, if you upload that, we can check whether there might be an issue with the import.)

Forest
625 post(s)
#22-Sep-17 09:47

My fault, the unique ID field had duplicates in it. Thanks Tim

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.