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