Base - cannot change the properties of a field in a table

English
, , ,
#1

Hi,

I don’t seem to be able to change the properties of a field in a table.

Example 1: unable to change ‘AutoValue’ to Yes once the table has been saved (even with empty table)

  • make new table > add field “ID” > set to Integer > make it the primary key > save table
  • now change AutoValue to Yes > save table → Warning: the column “ID” could not be saved. should the column be deleted and a new column appended? → answering ‘yes’ results in another warning: Error while saving the table design. → unable to save the changes in the table

Example 2: unable to change ‘Entry required’ to Yes when table contains data

  • make new table > add a field and make it the primary key
  • add new fields: ‘field1’, ‘field2’, ‘field3’
  • save table
  • enter data for 1 record
  • edit the table > set field1 to required=Yes
  • save table → Warning: the column “ID” could not be saved. should the column be deleted and a new column appended? → answering ‘yes’ results in another warning: Error while saving the table design. → unable to save the changes in the table > close the table without saving
  • edit the table → field1 is now gone
  • If you now close the database without saving the changes, then field1 will still be there, but if you close the database while saving the changes, then field1 is gone forever.

Example 3: changing ‘Entry required’ to Yes in empty table changes the order of the fields

  • make new table > add a field and make it the primary key
  • add new fields: ‘field1’, ‘field2’, ‘field3’
  • save table
  • enter data for 1 record
  • edit the table > set field1 to required=Yes
  • save table > → Warning: the column “ID” could not be saved. should the column be deleted and a new column appended? → The only option is to accept appending the column. This messes up the order of the fields. I know this does not affect the functionality of the table, but maintaining a table is much easier when the fields are in a logical order, just like it is much easier to find which letter of the alphabet is missing when the letters are properly sorted.

Questions:

  • Is all this expected/intended behaviour?

  • Is there an easy way to change the order of fields? Can fields be dragged to a new position? Is it possible to insert a row at a desired location?

  • Is it possible to export the design of a table as an SQL statement that can be edited and re-used, so that I don’t have to manually recreate the entire table every time I need to change an attribute of a field?

Using LO 6.2.8.2 on Linux Mint 18.3

tnx!

#2

Hello,

First, you have not specified database involved. Best guess is that this is Firebird embedded.

Second. Please limit questions to one at a time. Here you have multiple. This gets buried and other will have a difficult time searching for answers when buried as this. Seem related but actually different - primary key auto increment; Entry required; Field order; SQL generation.

Now to some answers.

Is all this expected/intended behaviour?

Simple answer is NO. There is still development ongoing for Firebird and many issues are already filed as bugs. See → Bugzilla and search Firebird.

Is there an easy way to change the order of fields? Can fields be dragged to a new position? Is it possible to insert a row at a desired location?

This is multiple questions in itself.

To change the order of fields see → How to change the order of columns in table ?

Dragging fields seems to be a bug - seem to remember one posted but can’t find it currently.

Inserting column at desired position can be done in some databases with “FIRST” or “AFTER” but not in Firebird.

Is it possible to export the design of a table as an SQL statement that can be edited and re-used, so that I don’t have to manually recreate the entire table every time I need to change an attribute of a field?

No but using SQL you can list the specific field information. See → Can I view and edit Table as SQL like Query in libreOffice Base ?

Now as for setting auto increment after the table is created see tdf#112491

If records have not been yet added, you can change by using SQL. First drop the field:

alter table "YOUR_TABLE" DROP "YOUR_FIELD"

Then add the new field:

alter table "YOUR_TABLE" ADD "YOUR_FIELD" INTEGER generated by default as identity primary key;

After doing this, you need to save the Base file and close/re-open to see the change.

For the “Entry required”, again use SQL:

ALTER TABLE "YOUR_TABLE" ALTER COLUMN "YOUR_FIELD" SET NOT NULL

If there is data in the table when doing this and NULL fields in the column you are setting, you will get an error:

1: firebird_sdbc error:
*unsuccessful metadata update
*Cannot make field YOUR_FIELD of table YOUR_ABLE NOT NULL because there are NULLs present
caused by
'ALTER TABLE "YOUR_TABLE" ALTER COLUMN "YOUR_FIELD" SET NOT NULL '

Add data to all NULL fields for that column and re-run the SQL.

Also after doing this, you need to save the Base file and close/re-open to see the change.

Change existing DB primary key to autovalue
#3

I admire your patience. Sorry for the multiple questions in one post. Thanks for your extensive answer. (and yes, firebird embedded)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.