Is there an easy way to set defaults in a Split database?

Libre Office Version: 6.0.7.3
Build ID: 1:6.0.7-0 ubuntu 0.18.04.10
Desktop: Cinnamon 4.2.4 Distro: Linux Mint 19.2 Tina

JDBC - hsqldb

One of my tables seems to have lost it’s default value is there an easy way using SQL to tell it to set the default?
Pseudo code
UPDATE MASTER SET DEFAULT SM_COLOUR_ID = 0;

Hello,

While your question is straight forward, getting to the answer is somewhat complex. This is mainly due to the way Base and the Table GUI works. This answer uses a split database as fields cannot be altered (only through delete & creating of fields) and may apply to other databases with this condition.

Note there is a bug report on this - tdf#104375. A somewhat difficult read. Will try & relate here.

When you create a table using the GUI there is a place where you can enter a value for each field - Default value. This is somewhat misleading. This value is never set in the database as an actual value. Also it cannot be modified using SQL. So if set, only way to clear or change it, because can’t modify with split DB, is to delete & re-establish with correct value. This value is used in the display and insertion of new records. If not a ‘required’ field, you can clear the entry and the resulting field entry will be empty.

The `DEFAULT’ value for a field in a table cannot be set in the Base GUI. It can only be done through SQL either when the table is created or through an ALTER statement:

ALTER TABLE "YOUR_TABLE" ALTER COLUMN "YOUR_FIELD" SET DEFAULT YOUR_VALUE;

You can view table defaults with an SQL statement:

SELECT * FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS WHERE TABLE_NAME = 'TABLE_NAME'

which are in COLUMN_DEF field result.

In table view or on a form, in contrast to the ‘Default value’ in the GUI, the default value is not seen. If you enter a new record without a different value, the default will be inserted. If you want this to be empty you create with the default and then return to the record and delete the value.

Now to add to all this. If through the GUI you set the default and later decide a different default is wanted, you must delete the old field and create a new one with the correct value. You can use the ALTER statement to set the DB field default and can see it is set but the GUI value will override it.

The method used depends upon the individual. If you want to strictly go with how a DB operates, do not use the GUI default. Again, this is discussed with the fact you cannot modify fields once saved in a split database.

Hope this is not too confusing.

Not at all, thank you, the ALTER TABLE statement is what I was looking for.

I did want to add another statement - removing a default from field in a table:

ALTER TABLE "TABLE_NAME" ALTER COLUMN "FIELD_NAME" DROP DEFAULT;