-2

How do add a check constraint that a column contain 'y', 'n', or blank?

I'm new to MySQL and REGEX, so I'm eager to hear either how to do this, or if I should solve the problem a different way. As I move a database to MySQL, my thought is to avoid nulls to simplify selects.

I tried CONSTRAINT CHECK REGEXP [yn\s]. When I insert a space into the column, it tells me I violated the constraint. (Previously I tried [yn ], but that didn't work either.) I also haven't yet figured out how to enforce lowercase.

I read about ENUM, so perhaps that's a better way to handle this; though in a perfect world, I wouldn't add that complexity.

Or perhaps using nulls is just a better way to work in MySQL?

How should I handle a desire for yes/no/blank, or should I change my desire in a MySQL/node.js world?

Jim Thompson
  • 21
  • 9
  • 2
    I think it's a mistake to allow 'blank'. – Strawberry Oct 24 '20 at 15:29
  • About "I also haven't yet figured out how to enforce lowercase." , [In MySQL, SQL patterns are case-insensitive by default.](https://dev.mysql.com/doc/refman/8.0/en/pattern-matching.html) – Luuk Oct 24 '20 at 15:43
  • I've been working with MySQL a little longer now and I agree that it is not a good idea to allow a blank. For example, the default behavior is to trim blanks upon retrieving a char type column. – Jim Thompson Nov 03 '20 at 21:28

2 Answers2

0

Where is the value coming from? In many cases, VARCHAR will trim off trailing spaces, so you should not check for it.

REGEXP '^[ynYN]?\s*$'

Here's the explanation:

^,$ -- anchor at ends, forcing the regexp to consider the entire string
[ynYN] -- although COLLATION may handle case folding, the makes it clear
? -- the yes/no may be missing
\s* -- any number of trailing spaces, including zero

That is probably overkill.

Rick James
  • 106,233
  • 9
  • 103
  • 171
  • Thank you. This works. Is there a way to have it fail if trailing spaces are sent? – Jim Thompson Oct 24 '20 at 18:21
  • Also, thanks for your clear explanation. For anyone interested in the question of case-sensitivity, I think my solution to that will await MySQL version 8.1 which plans to provide case-sensitive collations for character set utf8mb4. – Jim Thompson Oct 24 '20 at 18:43
  • But do you want to allow 'n' but not 'N'? – Rick James Oct 25 '20 at 23:19
  • 1
    @JimThompson - Fail if trailing spaces? Remove the `\s*`. If you also want to require y/n: `'^[ynYN]$'` -- this fails for anything other than a single letter. – Rick James Oct 25 '20 at 23:21
  • In my ideal world, an attempt to insert 2 spaces into the column, it would generate an error rather than just insert the first space. It's not vital. The table wouldn't be compromised. But it would be a sign that the program didn't know what it was doing.I would prefer to accept only lower case for similar reasons or for aesthetics, but it's not vital and I think I understand that I need to await a collation update (expected in MySQL 8.1) to achieve that with what I think I understand is the recommended character set, utf8mb4. I'm new at all this, so perhaps I'll know better soon. – Jim Thompson Oct 26 '20 at 02:00
  • 1
    I've been working with MySQL a little longer now and I agree that it is not good form to try to store meaning from a single space character. For example, the default behavior of MySQL is to trim trailing blanks from a char type column. That behavior can be changed by enabling PAD_CHAR_TO_FULL_LENGTH SQL mode, but as I start, trying to work within default modes makes sense to me. – Jim Thompson Nov 03 '20 at 21:32
  • 1
    Note to others: "PAD..." is a new feature; beware of depending on it if you are using an older version. – Rick James Nov 04 '20 at 00:07
  • I've been using MySQL a little longer still, and other beginners might like to know that for the database I'm designing, I've also changed my original intention of avoiding null-permitting columns. – Jim Thompson Nov 05 '20 at 01:49
  • @JimThompson - In my opinion, _most_ columns should be declared `NOT NULL`. I wish the default were `NOT NULL`. On the other hand, a _few_ situations work nicely with `NULL` The first that comes to mind is a "people" table that includes date of birth and date of death. The alternative is a table of only death dates; then the `NULL` moves to the `LEFT JOIN` that will be needed. – Rick James Nov 05 '20 at 02:52
  • @JimThompson - As for "PAD..." as a similar situation. Most strings can be trimmed on the end without hurting an application. `VARCHAR` essentially mandates it. And `CHAR` is useful only when the strings are fixed-length -- without trailing spaces. – Rick James Nov 05 '20 at 02:55
  • 1
    @JimThompson - I think the whole Question is misguided. The Y/y/N/n/space should be turned into some flavor of yes/no. That is, the column should have only 2 possible values not 4 or 5. (OK, maybe 3 if there a need for NULL to indicate "not yet answered") That is, do not store Y or y, sanitize that into a single value meaning "yes". – Rick James Nov 05 '20 at 02:58
  • I agree with you. The whole question was misguided. I originally asked this question because I wasn't as familiar with MySQL and had a design plan that I no longer think makes sense. Thank you for your help. – Jim Thompson Nov 05 '20 at 16:22
  • Nulls create a quandry. In my problem domain, they are often desired and sometimes not permitted. I'm moving a database with dozens of tables and many columns. It would be nice to have a rule-of-thumb re: nulls so that while coding I didn't need to remember which permit null and which don't. For this problem domain, I think it will be easier to recognize which columns are NOT NULL without having to look it up if I default to permitting nulls in general. If the problem domain had less desired nulls, I'd go the other way. Experience may lead me to change that plan. – Jim Thompson Nov 05 '20 at 16:28
  • 1
    @JimThompson - A thought... Change the name of column `foo` to `foo_null` if it is a `NULLable` column. (Leave non-nullable column names alone.) – Rick James Nov 12 '20 at 15:42
0

Prior to MySQL 8, the regex library was stuck in 1993. If you're using MySQL 5.7 you have to use ^(y|Y|n|N| )$ groups with OR statements, instead of character classes.

coladict
  • 3,650
  • 1
  • 10
  • 21