Can't Add Primary Key

[jfp]

Created a new table with two columns, no primary key with the Design View. If I go back to edit the table I can add a PK on one of the first columns but it won't seem to save.

I've also found that I can't enter data into tables with no PKs. Sounds odd to me but I've created the same table with a PK and I'm able to enter data in to it.

[QuazzieEvil]

Try adding it manually--via SQL tool. (Tools -SQL... menu option.)
the syntax is :

Code: Select all

ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>]
    PRIMARY KEY (<column list>);

For a table named PropertySheet, the command could be something like this:

Code: Select all

ALTER TABLE "PropertySheet" ADD CONSTRAINT "PropertySheet_pk" PRIMARY KEY("ID")

The name can be any valid identifier name.

NOTE: You may need to refresh the tables (view-refresh tables) to view changes after running the command.

[jfp]

Thanks for the reply. This is just such basic stuff but for some reason not working.

From the command line I try

alter table "raw_times" add constraint "raw_times_pk" primary key ("lap_id")

and get

1: Attempt to insert null into a non-nullable column: column: lap_id table: raw_times in statement [alter table "raw_times" add constraint "raw_times_pk" primary key ("lap_id")]



My original problem still exists in that I don't seem to be able to use any of the data editor or a form to enter data unless the table has a primary key. I refuse to go to access though.

[QuazzieEvil]

have you inserted data (some how) into the table already? if so, you will have to delete it. A PK cannot be added if the column being used in the constraint contains null fields.

I suggest to create a new table with the primary key, then copy the data from the old table--if you have data that you cannot afford to lose.

[Horus]

Hi,

Me too, I could confirm this annoying bug in OOO 3.3

After trying for about 30 min I think I've found a work-around:

Please note that in my finding, it seems the only allowed field type for PK is integer ( really stupid)

1. Right-click the integer field to mark it as Primary key
2. Immediately go to Field Properties and change AutoValue to Yes
3. Save the table and answer "Yes" to the dialog about 'The column "XXX" could not be changed.....' (I suppose previous data in that column are destroyed!)
4. Immediately quit OOO Base to let it save everything (don't look back before it's saved!)

Now, when you reopen the save DB file, that field should be marked as PK.

Really stupid bug

[Arineckaig]

Really stupid bug

Though not sure to which feature the comment is made I have to wonder whether this is necessarily a bug. Base with its embedded HSQLDB will actually permit data types other than an integer to act as a primary key whereas any other data type for an auto increment field would be fairly pointless. Though permitted a data type that incurs the risk of duplicates should naturally be avoided for use as a primary key.

[Villeroy]

If it were a bug, it would be a bug in HSQLDB (and that program is very well written IMHO). Your field data are inapropriate to serve as a primary key. Your workaround deletes your data. If you would delete or adjust your data before calling the table designer there would be no problem with the new primariy key.

[Horus]

Really stupid bug

Though not sure to which feature the comment is made I have to wonder whether this is necessarily a bug.

My comment referred to the "Can't add primary key" bug once we answer "no" to the dialog asking us whether we'd like Base to automatically add a primary key for us or not.

Base with its embedded HSQLDB will actually permit data types other than an integer to act as a primary key whereas any other data type for an auto increment field would be fairly pointless. Though permitted a data type that incurs the risk of duplicates should naturally be avoided for use as a primary key.

Of course duplicate (values) are not allowed in primary key field, otherwise that's not a primary key. It's that simple.

[Horus]

Your field data are inapropriate to serve as a primary key.

I don't understand what you mean; I don't see how INTEGER data are "inappropriate to serve as primary key"

[Villeroy]

When there are duplicates or missing values (Null).

[Horus]

When there are duplicates or missing values (Null).

Well, first, you didn't see by your own eyes the data I have. How can you say that my data "are duplicates or missing values" and jump to the conclusion that they "inappropriate to serve as primary key"?

Second, this bug is there for a new table (ie no data!)

[DACM]

I see this anomaly quite often with new, empty tables -- but only through the Base GUI -- not when using Base > Tools > SQL... or when manually editing the .script file within the Base (.odb) file using 7-zip (which I tend to utilize interchangeably when it's faster than Tools > SQL..., but don't try this at home without a backup because it's like playing with fire).

The GUI anomaly is quite repeatable (at least in Windows 7 64-bit):
Create Table in Design View...
Add an "ID" INTEGER field, but don't specify AutoValue = Yes
Add another field
Save the Table > Should a primary key be created now? > No (because I intend to make the ID field the PK)
Go back to the ID field > right-click > Primary Key
Save the Table and the Primary Key disappears
Go back to the ID field > right-click > Primary Key
Save the Table and the Primary Key sticks

And that's just a quick example of the GUI problem -- which can become much more bull-headed with data -- even unique, non-NULL data in the ID column.

[Horus]

The GUI anomaly is quite repeatable (at least in Windows 7 64-bit):
1. Create Table in Design View...
2. Add an "ID" INTEGER field, but don't specify AutoValue = Yes
3. Add another field
4. Save the Table > Should a primary key be created now? > No (because I intend to make the ID field the PK)
5. Go back to the ID field > right-click > Primary Key
6. Save the Table and the Primary Key disappears
7. Go back to the ID field > right-click > Primary Key
8. Save the Table and the Primary Key sticks

The primary key sticks only when you save the file and QUIT the application and reopen it again. At least for me, it's like that: I could repeat indefinitely step 5, open the table to check and the primary key is never there.

Using SQL might be another workaround, but the bug is there and it should be fixed.

[DACM]

The GUI anomaly is quite repeatable (at least in Windows 7 64-bit):
1. Create Table in Design View...
2. Add an "ID" INTEGER field, but don't specify AutoValue = Yes
3. Add another field
4. Save the Table > Should a primary key be created now? > No (because I intend to make the ID field the PK)
5. Go back to the ID field > right-click > Primary Key
6. Save the Table and the Primary Key disappears

Steps 5 & 6 are repeated indefinitely...unless you save the file and QUIT the application and reopen it again.

You are correct. The GUI's 'key' symbol is not sufficient to validate an actual change to the table structure -- despite it "sticking" visually upon second try (steps 5 & 6 repeated). Indeed, the Primary Key is never established through the GUI in this case.

You should definitely report the "defect" to the OOo and LibO developer communities. But I wouldn't hold my breath with the current state of affairs. In the meantime, here's the SQL workaround:

Code: Select all

ALTER TABLE "Table1" ADD PRIMARY KEY ("ID")

Be sure to View > Refresh Tables after adjusting table structures through Base > Tools > SQL...

[Horus]

You should definitely report the "defect" to the OOo and LibO developer communities.

Yeah, that's my intention too.

Let's have a laugh.... I've been trying to register an account at
http://qa.openoffice.org/issue_handling ... ssion.html
since a week and I always got

Server Error
We're sorry, but something went wrong.
We've been notified about this issue and we'll take a look at it shortly.

[Horus]

OK, I've finally succeeded to sign up and posted the bug. Its URL is
http://openoffice.org/bugzilla/show_bug.cgi?id=118038

[serme]

Hi, I'm experiencing the same bug. My plan was to use a compound key which I couldn't do during table creation. My workaround was to copy the table to a new table, let it add an autonumber primary key, then open the table, set my compound key and delete the autonumber field.

[Horus]

I'm now using LibreOffice (v 3.4.5) because OpenOffice is dead (no flame war intended, but it's just the truth). So I'm not sure if you'll get any reply from this forum anymore.

I've just tried this bug in LibreOffice and as expected, it's still there. I might need to re-report the same bug to LibreOffice's bug website again.

[RoryOF]

New version Apache OpenOffice due this quarter. Like Mark Twain, reports of its death are greatly exaggerated.

[Horus]

Oh I see. So OpenOffice is actually called JesusOffice

Would there be a list showing the feature difference between OpenOffice and LibreOffice? Or developers in one world just don't care of what happens in another world?

[Villeroy]

Back on topic: The HSQLDB is the same in both office suites. It is fully documented by the developers on http://hsqldb.org/doc/guide/ch09.html
Data definition queries may fail for various reasons. There is a switch SET REFERENTIAL_INTEGRITY { TRUE | FALSE }; which may help to manipulate an existing table structure.

[holimac]

Hi, after looking for and not finding a suitable GUI solution I had a look at the HSQL guide http://hsqldb.org/doc/guide/guide.html
This seemed to work for me:

Code: Select all

ALTER TABLE "TableName" ADD COLUMN "PID" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY;

[Villeroy]

Hi, after looking for and not finding a suitable GUI solution I had a look at the HSQL guide http://hsqldb.org/doc/guide/guide.html
This seemed to work for me:

Code: Select all

ALTER TABLE "TableName" ADD COLUMN "PID" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY;

Adding a new primary key as auto-integer is one thing. Turning an existing primary key into an auto-incrementing one may raise other problems. In any case the table and all related tables should be empty or the foreign keys should be allowed to be updated according to the changing primary key. It's better to be very clear about the types of keys before putting any data into the tables.