Restricting values in nullable fields [closed]

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 ...";

edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-08-29 18:39:49.181199

Sort by » oldest newest most voted

Hi

To add this control on a "CTRL" field of table "Customers", use ToolsSQL 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

more

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')]

( 2016-02-04 21:52:28 +0200 )edit

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

( 2016-02-05 17:24:10 +0200 )edit

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.

( 2016-02-05 22:27:32 +0200 )edit

good news, thank you for the feedback :)

( 2016-02-08 18:18:28 +0200 )edit