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.
> 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
> 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+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