[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:
- 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.
- Then write the SQL to do an update query to move the old field data into the new field and execute it.
- 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).