Re: [libreoffice-users] Cannot add primary key after saving without primary key.

Bottom post:

On 10/22/2012 11:34 AM, David Smith wrote:
> Yes.  As you said, if you click no when it prompts you to create a key now.
> Not only can you not create a key later, but you also cannot add data to
> the table .   Again, I'm using the odb file for my database.  Would it not
> make sense to block the user from being able to click "no" for creating a
> primary key or fixing it so that a primary key can be created latter?  It
> really sounds like a bug because it's all too easy to get yourself into a
> bad situation where it's not clear why you cannot add records to the table.
> On Oct 22, 2012 2:20 AM, "Girvin R. Herr" <girvin.herr@sbcglobal.net> wrote:
>
> David Smith wrote:
>
> > > Software: Libreoffice base
> > > Version: 3.5.4.2 (Debian Wheezy)
> > > Subject: Cannot add primary key after saving without primary key.
> > >
> > > I've got a problem.  I'm trying to test some different table designs.
> > > Whenever I create a table without a primary key, I find that I cannot
> > > go back and add a primary key later.  When I first save the table,
> > > Libreoffice asks me if I want to create a primary key now.  I choose
> > > "no" because I'm experimenting with some different table designs and
> > > didn't want to commit to anything just yet.
> > > 1. Create table without primary key.
> > > 2. Save table.
> > > 3. Libreoffice asks if I want to create a primary key now.
> > > 4. I choose no.
> > > 5. I later then open the table and set the primary key.
> > > 6. I save the table.
> > > 7.  I open the table again and the primary key that I chose was not saved!
> > >
> > > I've never had this kind of problem with a database before. Is this a
> > > bug or a missing feature?
> > David,
> > I am using the MySQL server for my database and have seen similar
> > problems.  I recently created a new table, selected the key, but forgot to
> > set the "Autoincrement" attribute on the key before my first save of the
> > table structure.  I tried several things, such as moving the key to a
> > temporary field, but nothing allowed me to make the original key field
> > autoincrement.  Every time I saved the table, the autoincrement selection
> > on the key would revert to "No".  Since this was a new table and no data
> > was in it yet, I ended up deleting the table and recreating it with the
> > autoincrement set on the key before I first saved it.  I had similar things
> > happen to me with table structure key definitions before.  Once, I had to
> > use the MySQL Monitor program and some SQL statements to change key
> > definitions on existing database tables.  Not fun and error prone, but it
> > worked.  The odd thing is that there is no error message from the server
> > when these things happen, which makes me suspicious of Base or the JDBC
> > driver - even more so since you are using a different server.  The bottom
> > line is that as far as Base table keys go, you must set them up properly
> > before you do that first save of the new table structure.  In my
> > experience, non-key fields may be changed or added after the first save.
> >   The key field is special.
> > Hope this helps.
> > Girvin Herr
     Perhaps Base should not allow someone to create a table without a 
primary key. Perhaps, people ought to read the warning more carefully 
also. After all, why would someone save a table without such a key when 
it clearly states that data can not be entered into table without a 
primary key?
     But this does not solve the problem, does it? There is a solution: 
a SQL statement. With the database open, Tools -> SQL. This opens the 
"Execute SQL Statement" dialog.
     To add a primary key to a table, this is the "formula" to use:
> ALTER TABLE <tablename> ADD [COLUMN] <columnname> Datatype
> [(columnSize[,precision])] [{DEFAULT <defaultValue> |
> GENERATED BY DEFAULT AS IDENTITY (START WITH <n>[, INCREMENT BY <m>])}] |
> [[NOT] NULL] [IDENTITY] [PRIMARY KEY]
> [BEFORE <existingcolumn>];
     One thing to mention: the table name and column name (field name) 
should be in double quotes.

Here are some examples from simple to more complex. The table's name is 
Sample, and its field name is First Name.
1    ALTER TABLE "Sample" ADD COLUMN "ID" INTEGER PRIMARY KEY;
2   ALTER TABLE "Sample" ADD COLUMN "ID" INTEGER PRIMARY KEY BEFORE 
"First Name";
3   ALTER TABLE "Sample" ADD COLUMN "ID" INTEGER GENERATED BY DEFAULT AS 
IDENTITY (START          WITH 0);

     Enter the SQL statement in the "Command to execute" window at the 
top. YOU MUST END THE SQL STATEMENT WITH A SEMI-COLON!!! If you don't, 
it won't work. Then click the Execute button. If you get a "Command 
successfully executed." message, Close the dialog. Then close Base and 
reopen Base. You should now have a table with a primary key that you can 
enter data into.

Additional notes: The primary key added each time is ID. When you create 
a field, it is usually placed after all the rest of the fields. However, 
using BEFORE with an existing field name, it is placed before that 
field. When creating a primary key using Design View and selecting 
AutoValue as a Field property, the SQL equivalent is #3. HSQLDB uses 0 
as the default START value, but you can select any value you want.

Personal comments: A database should be well planned and designed before 
Base is ever opened to create it. As far as experiments are concerned, I 
periodically do this myself just to see what will happen. And if 
something does not go right, I know not to do that again.
     As I wrote in the "Getting Started with Base" (seems like years 
ago) in a Caution table: "Every table requires a Primary key field."

--Dan



--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted