Primary key fields not appearing in the Add New Record form

[MerielP]

To reproduce this bug, view a database table created by uploading a CSV file, and click the 'Add new record' button. Notice that the primary key field does not appear in the 'Add new record' form.

For a lot of tables, it's easy not even to notice that this has happened, as if the primary key is an auto increment field, ORDS will quietly complete it without you needing to do anything. But for those tables with any other kind of primary key, this essentially means it's impossible to add new records, as there's no way of completing the field, and ORDS won't let you save a record if the primary key field is blank.

I initially thought that this was just affecting databases on Staging, but a bit of further digging reveals that the same thing also happens on Dev. However, it only seems to apply to tables created by uploading a CSV file.

[scottbw]

When you import a CSV to create a table, ORDS adds an automatic primary key field to it. Presumably the way you change the primary key is to import a CSV, and then change this generated field in the schema designer to be non-automatic?

[MerielP]

You can do it that way. But an imported CSV file may already contain a field you want to use as the primary key - so another option is to change the primary key from the generated field to a pre-existing one.

You can see an example of this on Staging, in the project 'Staging test 2', in the database 'CSV upload test 2'. This consists of three tables which were uploaded as CSV files, and then stitched together to make a relational database. In all three cases, I changed the ORDS-added primary key to a field that was in the file when I uploaded it ('catalogue_no', 'id', and 'place' - in two of the tables, I've also deleted the field added by ORDS). 'place' is a non-numeric primary key, and consequently it's currently impossible to add new records to this table.

[scottbw]

I tried doing this on Dev but I couldn't change the primary key field without getting some weird errors from the schema designer :(

[MerielP]

Is the problem you're encountering when trying to change the primary key the one described in issue #777, or a different one? I'm in the process of trying to write some documentation for changing primary keys, so it would be useful to know if there are potential pitfalls I haven't yet stumbled into...

[scottbw]

Ah, I think it was #777. Right, now I've done it, and yes, weirdly enough, the PK field I used has vanished.

For my test I uploaded a JACS3 coding table, and made "code" the primary key. I now can't add a record as it only shows the description field and not the code field.

[MerielP]

Scott - I've just realized you're not actually a member of the test project I mentioned above. Would it be useful for you to be? This may be superfluous now as you've managed to reproduce the bug elsewhere - but if you'd like me to add you, let me know which email address to use.

[scottbw]

No its fine, as I can reproduce the bug completely its not necessary.

[scottbw]

Phew, its not a problem on ORDS2 thankfully! (But #777 still is)

[thestoat]

Can you confirm this is a critical issue? Just wondering since, if this isn't a problem on ORDS2, and we are moving to ORDS2 ...

[MerielP]

I think this still counts as critical. As it can make it impossible to add records to some tables, it has the potential to cause serious problems, and hence we don't really want to do a new build to App until this is fixed.

Although the plan is ultimately to move to ORDS2, that won't be for a while - so the current aim is still to do at least one more build of ORDS1 to get the ODBC functionality live.

[thestoat]

I have altered the code so that the primary index is displayed when adding a new record. Is that sufficient?

[MerielP]

Excellent - all seems to be working as it should now.