Altering field properties in Base

I am missing something very basic, no doubt, but I can’t discover how to edit field properties once data is in a field.

My specific need is to go back and change several Boolean fields to remove the ‘null’ option. I have not found a way of getting to the field properties from the original table. I have tried it via a form design but even though I select ‘Input required’ to ‘Yes’ nothing changes in the original table (which contains large numbers of black squares because I neglected to specify the field properties properly in the first place).

I have tried to export the original table to a spreadsheet and re-import it but that just introduces new problems associated with failure to generate primary keys - I’m still working on that, but would much prefer to find a simple way of finding a way to change the field properties at source. Perhaps it’s not possible? I wouldn’t be too surprised if that was the case, since I accept it’s not really a great idea in general. Any suggestions gratefully received.

Open the Table in Edit mode. You can then change the property of the Boolean fields. You can change to it needing an input and the default to Yes or No from null. You will see this text in the box at the bottom right -
Select a value that is to appear in all new records as default. If the field is not to have a default value, select the empty string.

This change will only apply to new records as it cannot change existing records.

EDITED 27/01/2017

@JohnJeffrey Adding a new Boolean field should work. You could try running an UPDATE SQL query to change all the NULS to TRUE or FALSE (on a copy of database). Then try setting the Boolean field properties to NOT NULL.

UPDATE "Table1" SET "Your field name" = TRUE WHERE "Your field name" IS NULL

[Updated]

By default new fields are set to allow nulls. And adding a field to an existing table, by default fills it initially with nulls, unless DEFAULT is set (as the other answer here describes).

You can investigate your particular table with something like this code (replace “Samples” with the name of your table):

SELECT  "COLUMN_NAME",  "TYPE_NAME" As "Type",  "COLUMN_SIZE" As "Width",  "COLUMN_DEF" AS "Default Value", NULLABLE
	FROM "INFORMATION_SCHEMA"."SYSTEM_COLUMNS"
	WHERE "TABLE_NAME" ='Samples' 
	ORDER BY "ORDINAL_POSITION"

In the LO user interface, too bad there isn’t the option to create a non-null field, but there isn’t.

So I did a little testing, and a little reading.

First, in fact when a column has any nulls in it, the following ALTER SQL won’t work (where Flag is a boolean field):

ALTER TABLE "Samples" ALTER COLUMN "Flag" SET  NOT NULL

But this works just fine.

ALTER TABLE "Samples" ALTER COLUMN "Flag" SET  NULL

Here are three alternative ways of going forward:

A) Add a new field, but make sure it has a default set. True is what I would use. Then change it to SET NOT NULL. Then move your data into it using IFNULL or the like. Then delete old field and rename new to old.

B) You could copy your table structure, but not the data to a new table. Then set your boolean field to SET NOT NULL, then write an SQL query to update the new table with the old table’s data, making sure to use something like IFNULL(…) for to convert the boolean field(s). Then delete old table, and rename new to old name.

C) You could add a boolean field to your old table, then set it to all TRUE (to overwrite the initial nulls). Then set that new field to SET NOT NULL, and finally copy the old field into the new field using IFNULL.

D) Or possibly you can add a new field to your old table making sure to SET NOT NULL at the time it’s created, using this:

ALTER TABLE <tablename> ADD [COLUMN] <columnname> Datatype
    [(columnSize[,precision])] [{DEFAULT <defaultValue> |
    GENERATED BY DEFAULT AS IDENTITY (START WITH <n>[, INCREMENT BY <m>])}] |
    [[NOT] NULL] [IDENTITY] [PRIMARY KEY]
    [BEFORE <existingcolumn>];

And then delete the old field, rename the new to old name.

Thanks for the Q. I learned quite a bit in chasing this down.

was:

  1. Edit the table and for each field you need to fix, create a new additional field which does not allow nulls, and give it a new name, like -new.
  2. Then write the SQL to do an update query to move the old field data into the new field and execute it.
  3. When you’re done with this move and are happy that your data is now copied into the new fields, I recommend you first backup your database, then delete the old fields, and then rename the new field, to the old name.

(If you need more specific help on how to do the SQL just ask.)

I think the problem with the approach you are taking is that the field downgrade can’t occur as long as there are any records with nulls in those fields. I’ve also seen other reasons one can’t seem to edit fields.

It might also be good if you would edit your question here to include which database you are using, because the SQL can vary at times depending on this (i.e. the functions you can use to process the nulls into non-null values).

Thanks, EasyTrieve. The information that “field downgrade can’t occur as long as there are any records with nulls in those fields” is clearly my basic problem, and I had already begun to suspect it!
I am happy to edit the question if you think it would be useful, but I’m not sure exactly what information would help. I am using Libre Office 5.1.6.2 and the database I have constructed just uses ‘Base’. The original data was in a spreadsheet but that is long gone.

I have hit yet another snag while trying to follow your advice. I selected ‘Edit’ and created a new field which does not allow nulls. When I try to save I get the message:
Error while saving the table design. Column constraints are not acceptable in statement [ALTER TABLE “Table1” ADD “Printoutx” BOOLEAN NOT NULL].
All I can think of now is to export the data to a spreadsheet, delete the table, and reimport the data to a redesigned table with the same field names.