Restricting values in nullable fields

[PeterOL]

Is there any way in LO Base to set a constraint of the form "This field is not required, but if given must be ‘X’ "?
I have a one-character text field and have tried all sorts of ways to do it.

• If I SET NULL and then ADD CONSTRAINT … CHECK (“field-name” = ‘X’) I get “Check constraint violation”;
• If I ADD CONSTRAINT … CHECK (“field-name” IN (‘X’, ‘’)) [two consecutive single quotes] I get “Unexpected token ;, requires )”;
• If I ADD CONSTRAINT … CHECK (“field-name” IN (‘X’, ’ ')) [space between the last two single quotes] I get the same thing;
• If I ADD CONSTRAINT … CHECK (“field-name” IN (‘X’, NULL)) I get “NULL in value list statement …”;

[PYS]

Hi

To add this control on a “CTRL” field of table “Customers”, use Tools▸SQL to execute:

alter table "Customers" ADD CONSTRAINT "CHECK_X" CHECK  ("CTRL"='X')

And to remove you can do:

alter table "Customers" drop constraint "CHECK_X"

Note: CHECK_X is the constraint name I gave to the constraint. This is not mandatory but it makes its possible to delete by name…

Regards

[PeterOL]

Thank you for the suggestion, but I don’t think you read the question carefully. As stated above, if the field is nullable, when I do that i get the following:
1: Check constraint violation in statement [ALTER TABLE “People” ADD CONSTRAINT “CK_SILENT” CHECK (“Phone_silent”=‘S’)]

[PYS]

This response makes me sad… I always try to understand the issues and I always test my proposals. The message you report likely indicates that the table contains records do not satisfy the constraint: any value <> S (which can be a space or any character). You can find them with:

SELECT "Phone_silent" FROM "People" WHERE NOT "Phone_silent" IS NULL AND "Phone_silent" <> 'S'

Regards

[PeterOL]

Sorry, Pierre-Yves, I appreciate your help and I didn’t mean to offend. I only said that because I had stated in the original question that I had tried “ADD CONSTRAINT … CHECK (“field-name” = ‘X’)”.

It’s a new table just created, and has no records in it.

Anyway the problem is now resolved. I deleted the field and recreated it and the constraint can now be applied, so I think there must have been some corruption somehow in the database. Thank you for your help.

[PYS]

good news, thank you for the feedback