Ask Your Question
0

Restricting values in nullable fields

asked 2016-02-04 07:39:18 +0200

PeterOL gravatar image

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-02-04 15:25:16 +0200

pierre-yves samyn gravatar image

updated 2016-02-04 16:22:15 +0200

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

edit flag offensive delete link more

Comments

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

PeterOL gravatar imagePeterOL ( 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

pierre-yves samyn gravatar imagepierre-yves samyn ( 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.

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

good news, thank you for the feedback :)

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-02-08 18:18:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-02-04 07:39:18 +0200

Seen: 72 times

Last updated: Feb 04 '16