Re: [libreoffice-users] Cannot add primary key after saving without primary key.
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.
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.h...@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 -- For unsubscribe instructions e-mail to: users+help@global.libreoffice.** org users%2bh...@global.libreoffice.org Problems? http://www.libreoffice.org/**get-help/mailing-lists/how-to-** unsubscribe/http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.**documentfoundation.org/** Netiquette http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.**libreoffice.org/global/users/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.
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
[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
Re: [libreoffice-users] Cannot add primary key after saving without primary key.
Hi :) Which back-end are you using to store the data? MySql/MariaDb, Postgresql, Sqlite? Regards from Tom :) From: David Smith sidic...@gmail.com 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.
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 tomdavie...@yahoo.co.uk wrote: Hi :) Which back-end are you using to store the data? MySql/MariaDb, Postgresql, Sqlite? Regards from Tom :) From: David Smith sidic...@gmail.com 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.
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.
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 sidic...@gmail.com To: Tom Davies tomdavie...@yahoo.co.uk Cc: users@global.libreoffice.org 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 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
