Can't add primary key into table

English
#1

Hello,

It seems that nothing works with FireBird database.
I try to insert PRIMARY KEY according to examples I’ve fond on the web, but SQL fails.
Here is my SQL request
ALTER TABLE table1 ADD PRIMARY KEY (ID)

… and here is result:

Error1:
Statut SQL: HY000
Code d’erreur: 1000
Erreur de syntaxe dans l’expression SQL

Error2:
Statut SQL: HY000
Code d’erreur: 1000

Error3:
Statut SQL: HY000
Code d’erreur: 1000
syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

Any comments ?

#2
#3

On the link you provided an expert suspects that there are problems with Java.
I checked my system: Java 8 is installed. Is it Ok for FireBird database handling ?
Thanks.

#4

Have you in the menu Extras>Options>LibreOffice>Advanced, there in “Optional features”, the “Experimental features” turned on? Which OS and LO-Version do you use?

#5

No, for the moment this option is OFF. I’ll try putting it to “ON”.
My OS: Windows 10 PRO. Concerning LO, I don’t know how to check it.

#6

I’ve just tried executing of the command
ALTER TABLE table1 ADD PRIMARY KEY (ID)
with “Optional features” set to ON, but it didn’t help: the errors are the same as before

#7

Check the LO version in the Help>About LibreOffice menu. Maybe one of the bug reports will help you.

#8

The LO version is: 6.1.3.2 (x64)

#9

Hello,

Firebird embedded is still undergoing modifications to have it working fully in an easy manner. Until then some of the functions need to be worked around but with these alternatives, Firebird does work very well.

For your situation there are two items. Based upon a comment here → How to change the order of columns in table ?, you need to do this from the main Base menu item ‘Tools->SQL…’ and also use this statement:

alter table "table1" add constraint PK_MY_TABLE primary key (ID)

Once completed, save the Base file (with Firebird embedded changes are not committed until the Base file is saved), then close all LO files & re-open the Base file. You should now find the table has the selected field set as primary key.

There are also other methods. One is to delete the old field & enter a new one & set as primary key. Another is to copy/paste the table and when pasting set the appropriate field as a primary key.

#10

Thanks Ratslinger.
This solution doesn’t work either:
link text

#11

Have re-tried this multiple times on Mint18.3 with LO v6.1.4.2 without problems. Possibly a LO Windows version problem. Best to file Bug report.

Are you certain you have the correct Table and field name?

#12

Well, I’ve just created a new table table2 with only two columns “aaa” and “bbb” and have tried to add a primary key. Here is the result:
link text

Here is whole content of “State” field:
*1: firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE table1 failed
Unknown columns in index PK_MY_TABLE
caused by
‘alter table “table1” add constraint PK_MY_TABLE primary key (ID)’

#13

It seems that it finally worked: first I added the new column and then applied the constraint. But when I open the table in “Edit” mode I don’t see any sign that confirm that ID column is actually primary key.

#14

Moreover, when I open table2, there is no any options for adding new records:
link text

#15

@Pavel_47 Not certain as to what you are now doing. In your comment you are trying to modify “Table2” but the SQL shows “Table1”.

If you get a successful execution, you need to save, close all open LO & the re-open Base file.

#16

Finally it worked. Thanks.
But some misunderstandig persists nevertheless. It seems that it’s impossible to change the properties of primary key. When I try to apply “autogeneration”, the error occurs: link text

#17

Well … it worked. Then I tried some manipulations with primary key in edit mode (e.g. applying “autogeneration” option). It didn’t work … moreover the primary key ID disappeared. Then I’ve tried to add column ID once more, but I couldn’t !!! link text

#18

It seems I understood the cause of error: if a table isn’t empty, any modification of the primary key properties is impossible.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.