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

[Mark Stanton]

Whilst it is recommended that every table should have a primary key 
field, as far as I know HSQLDB is the only engine to enforce this by 
refusing to allow changes to tables not having one.

I certainly think that the code ought to be fixed so that database 
engines that require the field shouldn't allow table creation without 
it.

Regards
Mark Stanton



-- 
For unsubscribe instructions e-mail to: users+h...@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

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

[David Smith]

> Here are some examples from simple to more complex. The table's name is
> Sample, and its field name is First Name.
> 1ALTER TABLE "Sample" ADD COLUMN "ID" INTEGER PRIMARY KEY;

Strange.. It said it was completed successfully.  When I do this, I
can now see the ID column and it appears I can add records. However,
when I go into the design view for the table, there is no ID column or
primary key columns and it shows only the columns I had before.  It
also throws an error when I try to put in any records. "Attempt to
insert null into non-nullable column "?".But I don't have any
columns labeled "?".



> 2   ALTER TABLE "Sample" ADD COLUMN "ID" INTEGER PRIMARY KEY BEFORE "First
> Name";

This one executes just fine as well.. However I cannot add records and
the "ID" column is not visible in the design view of the table.


> 3   ALTER TABLE "Sample" ADD COLUMN "ID" INTEGER GENERATED BY DEFAULT AS
> IDENTITY (START  WITH 0);
>

This is the only one that successfully allows me to add records and I
see the auto-increment column on the end.  However, the ID column is
not showing up in design view so if I change the key from ID to
something else, I can't change it back to ID?

Also, there is a problem with adding auto-incrementing fields using
the design view.  It just doesn't allow you to do it if the
auto-incrementing field isn't the first field in the table.  With your
SQL, I now see an auto-incrementing field at the end of the table
which is pretty nice, and something I was trying to do earlier.

Should I be filing bug reports?


>  As I wrote in the "Getting Started with Base" (seems like years ago) in
> a Caution table: "Every table requires a Primary key field."

In that case, it makes a lot of sense to simply prevent the user from
saving a table without a primary key from the design view.  In the
past, I have data copies from applications such as libreoffice calc
and sometimes there is a lot of duplicate data in there and I've
always gotten away with other DBMS by just cramming it into a database
table and not setting a primary key at all. Setting a primary key
would cause the insertion to fail due to duplicate records.  After
that, pulling only the unique records and joining with other tables..
Effectively having the database itself do the de-duplication of data
and picking up other data at the same time.  Which then goes back to
libreoffice calc and then the table I created without a primary key
gets deleted.

  Of course, I can set a primary key that is just an auto-incrementing
number if it makes libreoffice happy, I just haven't seen a need for
it on this database table.  It seems like a waste of a column since
other DBMS don't require it. I don't care either way.

-- 
For unsubscribe instructions e-mail to: users+h...@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

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

[Dan Lewis]

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"  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  ADD [COLUMN]  Datatype
[(columnSize[,precision])] [{DEFAULT  |
GENERATED BY DEFAULT AS IDENTITY (START WITH [, INCREMENT BY ])}] |
[[NOT] NULL] [IDENTITY] [PRIMARY KEY]
[BEFORE ];
 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.

1ALTER 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 

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

[David Smith]

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"  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
>
> --
> 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
>
>

-- 
For unsubscribe instructions e-mail to: users+h...@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

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

[Girvin R. Herr]

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

--
For unsubscribe instructions e-mail to: users+h...@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

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

[Tom Davies]

Hi :)
Thanks :)  So the data itself is stored inside the Base file.  There is only 1 
file.  Base is not just the front-end connecting you to an external back-end 
with the data in it.  Like it would be if using MS Access instead of LO Base.  

It's the way i would have approached Base before reading a lot of the comments 
on this list that suggest having the data in a separate back-end is much, much 
better.  At the moment i am a bit perplexed about how to use Base but it 
'should' be possible to just add an exra field,especially a primary key!  I'm 
just not sure how to do so.  I tried looking it up in the Faq
http://wiki.documentfoundation.org/Faq/Base/009
but that didn't seem aprticularly helpful!  The main page of the Faq is here
http://wiki.documentfoundation.org/Faq#Base
There is an introduction to Base in the Getting Started Guide
http://wiki.documentfoundation.org/Documentation/Publications
but only the 1st chapter is done for the main guide
http://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Guide

Hopefully one of the database experts will be able to help you soon.  
Apols and regards from
Tom :)  





>
> From: David Smith 
>To: Tom Davies  
>Cc: "users@global.libreoffice.org"  
>Sent: Sunday, 21 October 2012, 12:16
>Subject: Re: [libreoffice-users] Cannot add primary key after saving without 
>primary key.
> 
>> I used the Libreoffice wizard to create the database. It's a local
>> file. It's an offline laptop and I don't have MySQL or PostgresSQL
>> running so maybe it's SQLite?  What's the process to check the
>> backend?
>
>Ok,
>Database File is an .odb.  Open Document Database.
>
>Thanks for your time.
>
>-- 
>For unsubscribe instructions e-mail to: users+h...@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
>
>
>
>
-- 
For unsubscribe instructions e-mail to: users+h...@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

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

[David Smith]

> I used the Libreoffice wizard to create the database. It's a local
> file. It's an offline laptop and I don't have MySQL or PostgresSQL
> running so maybe it's SQLite?  What's the process to check the
> backend?

Ok,
Database File is an .odb.  Open Document Database.

Thanks for your time.

-- 
For unsubscribe instructions e-mail to: users+h...@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

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

[David Smith]

I used the Libreoffice wizard to create the database. It's a local
file. It's an offline laptop and I don't have MySQL or PostgresSQL
running so maybe it's SQLite?  What's the process to check the
backend?

Thanks for your time.


On Sun, Oct 21, 2012 at 5:45 PM, Tom Davies  wrote:
> Hi :)
> Which back-end are you using to store the data?  MySql/MariaDb, Postgresql,
> Sqlite?
> Regards from
> Tom :)
>
>
> 
> From: David Smith 
> To: users@global.libreoffice.org
> Sent: Sunday, 21 October 2012, 10:40
> Subject: [libreoffice-users] Cannot add primary key after saving without
> primary key.
>
> 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?
>
> --
> For unsubscribe instructions e-mail to: users+h...@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
>
>
>

-- 
For unsubscribe instructions e-mail to: users+h...@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

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

[Tom Davies]

Hi :)
Which back-end are you using to store the data?  MySql/MariaDb, Postgresql, 
Sqlite?  

Regards from
Tom :)  





>
> From: David Smith 
>To: users@global.libreoffice.org 
>Sent: Sunday, 21 October 2012, 10:40
>Subject: [libreoffice-users] Cannot add primary key after saving without 
>primary key.
> 
>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?
>
>-- 
>For unsubscribe instructions e-mail to: users+h...@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
>
>
>
>
-- 
For unsubscribe instructions e-mail to: users+h...@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