Base-restoring primary key

[fatngristle]

I had a simple table, numeric autokey, text, longmemo. I decided i wanted the text field to be keyed and unique but couldn’t figure out how to make it disallow duplicates, so i made it the primary key and thought that it accepted that so i deleted the numeric key. I had not saved or something because when i closed, it threw errors. I no longer have a keyed field and i found the single duplicate that caused the failure, but i can’t delete it without a key. I tried creating a new key field but it doesn’t like that.
I’ve closed/opened and apparently it saved what i did; no keyed field.

What is the best way to fix this?
Thank you.

Version: 7.0.0.3 (x64)
Build ID: 8061b3e9204bef6b321a21033174034a5e2ea88e
CPU threads: 8; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL

[Ratslinger]

Hello,

Probably the best first step is to get a valid key. Will guess that your original auto-increment field is no longer there. If it were, you could simply edit the table and set this as the key once again. Setting a text field as the primary key is not a good idea as most databases will add an additional internal numeric field to compensate.

So an easy way to add a new primary key field is to copy/paste the table. Right mouse click the table name and select Copy. Then select a blank area and right mouse click and select Paste. Will have a similar screen to this:

Insure Table name is unique, and both Definition and data and Create new field as primary key are selected. Enter a unique field name for the primary key in the Name: area. Should be able to select Create button and the table with the new key field will be created.

As for your unique text field, edit the table. Then from the menu, select Tools->Index Design.... Then select New Index icon:

You can assign the index name, set as Unique and pick the field it applies to.

[fatngristle]

Thank you.
After pasting to create the new table and selecting the options as you’ve said i get an error:

“An error occurred. Do you want to continue copying?”

if I click MORE, this is the detail:

 SQL Status: 37000
 Error code: -16

 Wrong data type: java.lang.NumberFormatException: For input string: "UDP"

If i click NO, i’m left with a table with 2 fields (no new keyed field) and no data in the new table.
If YES, the error comes back with each click, I’m guessing once for each line in the database as the ‘input string’ of each MORE error detail changes, matching the word in the text field that i tried to key.

  SQL Status: 37000
  Error code: -16

  Wrong data type: java.lang.NumberFormatException: For input string: "TCP Segment"

I can open the original table and see all my data (only 2 fields, 86 lines) but i can’t select, edit or copy the data. If i could export or just copy the data into a spreadsheet, i could pull it back into a table.
Suggestions?

[fatngristle]

Nevermind, i got that part. I pasted special RTF on the blank table2 and it created a new table dialog as your first picture showed, but this time it worked. It gave an error about something being too long. When i looked in the new table, it had a keyed numeric Double field but it was not automatic (Didnt have that option) so i changed it to integer and set auto and it’s working again.

Now onto your second instruction:
I get this error -
Violation of unique index “index1”: duplicate value(s) for column(s) “Term” in statement [CREATE UNIQUE INDEX “index1” ON “Table1” ( “Term”)]

I removed the one duplicate field, however i have fields that have duplicate words, eg “Spam” & “Spam Salad”
Are spaces allowed in an indexed field?

[Ratslinger]

@fatngristle,

The entire field is considered when determining duplicates. Therefore:

“Spam” & “Spam Salad”

are each unique. Just performed similar test - no problem encountered.