Ask Your Question

Can't add primary key into table

asked 2019-01-29 10:27:57 +0100

Pavel_47 gravatar image

updated 2019-01-29 10:47:32 +0100

ebot gravatar image


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 ?

edit retag flag offensive close merge delete


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.

Pavel_47 gravatar imagePavel_47 ( 2019-01-29 14:54:57 +0100 )edit

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?

ebot gravatar imageebot ( 2019-01-29 15:03:07 +0100 )edit

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.

Pavel_47 gravatar imagePavel_47 ( 2019-01-29 15:13:05 +0100 )edit

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

Pavel_47 gravatar imagePavel_47 ( 2019-01-29 15:16:07 +0100 )edit

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

ebot gravatar imageebot ( 2019-01-29 15:24:28 +0100 )edit

The LO version is: (x64)

Pavel_47 gravatar imagePavel_47 ( 2019-01-30 08:30:44 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-01-29 16:29:57 +0100

Ratslinger gravatar image


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.

edit flag offensive delete link more


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

Pavel_47 gravatar imagePavel_47 ( 2019-01-30 08:29:55 +0100 )edit

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?

Ratslinger gravatar imageRatslinger ( 2019-01-30 11:38:46 +0100 )edit

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)'

Pavel_47 gravatar imagePavel_47 ( 2019-01-30 12:30:17 +0100 )edit

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.

Pavel_47 gravatar imagePavel_47 ( 2019-01-30 12:41:15 +0100 )edit

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

Pavel_47 gravatar imagePavel_47 ( 2019-01-30 12:44:36 +0100 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2019-01-30 16:23:28 +0100 )edit

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

Pavel_47 gravatar imagePavel_47 ( 2019-01-31 10:29:57 +0100 )edit

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

Pavel_47 gravatar imagePavel_47 ( 2019-01-31 10:43:12 +0100 )edit

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

Pavel_47 gravatar imagePavel_47 ( 2019-01-31 10:55:25 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-29 10:27:57 +0100

Seen: 1,000 times

Last updated: Jan 29 '19