Can't Add Primary Key

Discuss the database features

Can't Add Primary Key

Postby jfp » Sat Apr 16, 2011 8:49 am

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.
Office 3.3 on Windows
jfp
 
Posts: 2
Joined: Sat Apr 16, 2011 8:43 am

Re: Can't Add PK

Postby QuazzieEvil » Sat Apr 16, 2011 4:34 pm

Try adding it manually--via SQL tool. (Tools -SQL... menu option.)
the syntax is :
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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.
QuazzieEvil
Volunteer
 
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Can't Add Primary Key

Postby jfp » Sat Apr 16, 2011 5:51 pm

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.
Office 3.3 on Windows
jfp
 
Posts: 2
Joined: Sat Apr 16, 2011 8:43 am

Re: Can't Add Primary Key

Postby QuazzieEvil » Sat Apr 16, 2011 7:46 pm

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.
QuazzieEvil
Volunteer
 
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Can't Add Primary Key

Postby Horus » Wed Apr 27, 2011 8:51 pm

Hi,

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

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 ( :shock: 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 :evil:
Horus
 
Posts: 86
Joined: Mon Apr 21, 2008 12:21 pm

Re: Can't Add Primary Key

Postby Arineckaig » Thu Apr 28, 2011 2:39 pm

Really stupid bug :evil:

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.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Can't Add Primary Key

Postby Villeroy » Thu Apr 28, 2011 2:51 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27574
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't Add Primary Key

Postby Horus » Fri Apr 29, 2011 1:55 am

Arineckaig wrote:
Really stupid bug :evil:

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.

Arineckaig wrote: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
 
Posts: 86
Joined: Mon Apr 21, 2008 12:21 pm

Re: Can't Add Primary Key

Postby Horus » Fri Apr 29, 2011 2:06 am

Villeroy wrote: 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"
Horus
 
Posts: 86
Joined: Mon Apr 21, 2008 12:21 pm

Re: Can't Add Primary Key

Postby Villeroy » Fri Apr 29, 2011 3:14 pm

When there are duplicates or missing values (Null).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27574
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't Add Primary Key

Postby Horus » Fri Apr 29, 2011 3:40 pm

Villeroy wrote: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!)
Last edited by Horus on Sat Apr 30, 2011 1:44 pm, edited 1 time in total.
Horus
 
Posts: 86
Joined: Mon Apr 21, 2008 12:21 pm

Re: Can't Add Primary Key

Postby DACM » Fri Apr 29, 2011 10:36 pm

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.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Can't Add Primary Key

Postby Horus » Sat Apr 30, 2011 1:52 pm

DACM wrote: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.
Horus
 
Posts: 86
Joined: Mon Apr 21, 2008 12:21 pm

Re: Can't Add Primary Key

Postby DACM » Sun May 01, 2011 12:44 am

DACM wrote: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
Horus wrote: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   Expand viewCollapse view
ALTER TABLE "Table1" ADD PRIMARY KEY ("ID")

Be sure to View > Refresh Tables after adjusting table structures through Base > Tools > SQL...
Last edited by DACM on Fri Jan 10, 2014 2:21 am, edited 1 time in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Can't Add Primary Key

Postby Horus » Wed May 04, 2011 7:28 pm

DACM wrote: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.

:lol: :lol: :lol:
Horus
 
Posts: 86
Joined: Mon Apr 21, 2008 12:21 pm

Re: Can't Add Primary Key

Postby Horus » Tue May 17, 2011 7:40 pm

OK, I've finally succeeded to sign up and posted the bug. Its URL is
http://openoffice.org/bugzilla/show_bug.cgi?id=118038
Horus
 
Posts: 86
Joined: Mon Apr 21, 2008 12:21 pm

Re: Can't Add Primary Key

Postby serme » Sun Feb 05, 2012 1:32 am

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.
OpenOffice 3.3.0; Windows 7
serme
 
Posts: 1
Joined: Sun Feb 05, 2012 1:06 am

Re: Can't Add Primary Key

Postby Horus » Mon Feb 06, 2012 1:35 pm

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.
Horus
 
Posts: 86
Joined: Mon Apr 21, 2008 12:21 pm

Re: Can't Add Primary Key

Postby RoryOF » Mon Feb 06, 2012 2:49 pm

New version Apache OpenOffice due this quarter. Like Mark Twain, reports of its death are greatly exaggerated.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 30185
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Can't Add Primary Key

Postby Horus » Tue Feb 07, 2012 5:02 pm

Oh I see. So OpenOffice is actually called JesusOffice :lol:

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?
Horus
 
Posts: 86
Joined: Mon Apr 21, 2008 12:21 pm

Re: Can't Add Primary Key

Postby Villeroy » Tue Feb 07, 2012 5:21 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27574
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can't Add Primary Key

Postby holimac » Thu Feb 16, 2012 6:25 am

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   Expand viewCollapse view
ALTER TABLE "TableName" ADD COLUMN "PID" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY;
OpenOffice 3.3 on Windows 7
holimac
 
Posts: 1
Joined: Thu Feb 16, 2012 6:22 am

Re: Can't Add Primary Key

Postby Villeroy » Thu Feb 16, 2012 11:44 am

holimac wrote: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   Expand viewCollapse view
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27574
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests