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;
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
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
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: email@example.com
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
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy