I have no idea what you do. This is what I did:
- I imported an arbitrary record set from a test database into my spreadsheet template. Since this record set comes from a database, I do not have to bother about consistency issues.
- Formatted the date column with cell style “ISO Date”.
- I stored the spreadsheet in a trusted directory, so the little macro is allowed to run.
- I ran the macro and filled out the “form” on the second sheet for database type “HSQL”, including an additional primary key (3. option on my “form”, cell A9).
- Created a new database document with an embedded HSQLDB (any existing database with external or embedded HSQLDB should work as well).
- Copied the CREATE TABLE statement from the sheet.
- Ran it in the SQL dialog. Got a success message.
- menu:View>Refresh tables.
- Copied the record set from sheet.
- Pasted to the table icon and confirm the dialog.
My record set has 4 columns, the table has an additional auto-ID at position 5. This way the first 4 columns are filled with sheet data without touching the auto-ID at the 5th position.
If you are sure that you want to use an existing field as primary key, leave option 3 in my “form” blank and mark the column in row 27. If the cell validation does not allow this, the column data are not unique, which is a precondition for any valid primary key.
P.S. It is possible to paste data to an existing table that has no primary key. The result is a read-only record set.
The paste command is disabled (grayed out) only if the connected database is read-only. I can think of several reasons, why this may be the case.
- It’s a pseudo-database, e.g. spreadsheet.
- An embedded database is extracted to a temporary folder with no write access.
- User/Group priviledges imposed to the logged in database user by the database backend (DB servers such as MySQL).
A read-only database document can edit the connected database if that database it editable at all.
CAUTION: I can edit table contents of an embedded Firebird DB, even though the document is read-only. Writing back the embedded database to the embedding document fails silently. No changes are saved. Base should lock an embedded database for writing if the embedding document is not writable (like it does with embedded HSQL).
P.P.S. All this happens in a directory with write access on a local hard disk.