Advanced constraints in MySQL

English
#1

I’m trying to create an advanced constraint on a MySQL table. The intent is to ensure that if a certain integer field is null or 0, then a collection of boolean fields are not true. The field “REVIEWEDBY” is the integer field, and all other fields mentioned are the boolean fields. Here’s what I’ve tried:

ALTER TABLE workorders ADD CONSTRAINT CK_Rev_Selection
   CHECK (
       (REVIEWEDBY <> 0 AND REVIEWEDBY NOT NULL)
       OR (   REVDATERECEIVED = 0
          AND REVEVCUSTOMERPO = 0
          AND REVCONTRACT = 0
          AND REVQUANTITY = 0
          AND REVSPECIALINST = 0
          AND REVCUSTOMER_OVERRIDE = 0
          AND REVSHIPPINGADDRESS_OVERRIDE = 0
          AND REVSHIPINGADDRESS2_OVERRIDE = 0
          AND REVITEMSALL = 0
          )
         )

(code formatting edited by ajlittoz for better readability without scrollbar)

It is not preventing the situation it is intended to prevent.
I had originally attempted to force this by means of macros that would toggle the enabled status of the boolean controls on the form, but that very quickly became cumbersome and flaky.

Edit: I’ve tried a very simple analog of this at SQLFiddle. Here it is.

#2

Why didn’t you set (REVIEWEDBY > 0)?
An example with an internal database would be helpful.

#3

My mistake in posting. The > 0 approach is what I actually tried originally, and when it didn’t work, I tried the version I posted, which still didn’t work. My sqlfiddle example is a pretty self-contained example of my constraint not working.

#4

What I find perplexing is that retracted: I’m not finding any examples online of using conditional statements involving multiple fields like this for check constraints, and it is working for neither my MySQL database nor SQLFiddle, nor are either one throwing an error when I set up the constraint.

I did find one example, as described in a later comment.

#5

Is there any example working with Check constraint and such a construction with OR? Having a look at all descriptions of MariaDB and MySQL and all examples are checking only one condition, which has to be true or false - no combination with AND and OR.

#6

I guess the “Check” type of constraint is not built to accept or reject conditional statements with AND/OR operators. Surprising…

#7

I must retract this statement. I have found one. (The third code block on the page creates a constraint with AND. When I copy and paste this into SQLFiddle and try to insert a record that violates the constraint, it doesn’t prevent the insertion. Interestingly enough, when I try to show create table persons, there’s there’s no trace of the constraint, so even though the schema built with no errors, it did not build completely.)

#8

Hoping for you that some contributor will answer but this is not basically a LO question. It is rather an SQL one and you might be happier on a dedicated site.

#9

Hello,

The link you provided to W3Schools does not state to which version of MySQL it applies (at least as far as I can see).
.
My test shows that thin works in MySQL v 8.0.30 and does not work in v5.7.26
.
Also see → MySQL :: MySQL 8.0 Reference Manual :: 13.1.20.6 CHECK Constraints
.
You should always note your DB used(and version), LO version and OS. These can all have an effect of the answer. It also helps us from searching through you old questions just to se if you ever posted this before (you did but many questions ago - took time to find)
.
If you stick with v5.7.x, use a macro or trigger or something else (can’t say what currently).

#10

Have tried the example @agerber85 mentioned (w3schools). Have had problems to create the table, special the CHECK with phpMyAdmin. So I created the check

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

with LO and direct connection. The constraint of two fields will work. But it will work the same as two constraints.
Now testing the same with

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 OR City='Sandnes');

Will work also. You could save rows with Age=17 and City=‘Sandnes’, also with Age=18 and City=‘New York’, also with Age=18 and City=‘Sandnes’.
Then I tried

CREATE TABLE `test`.`Persons` (
    `ID` int NOT NULL PRIMARY KEY,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Age` int,
    `City` varchar(255),
    CONSTRAINT CHK_Person 
    CHECK (`Age`>=18 OR (`City`='Sandnes' AND `FirstName`='Robert'))
);

Will run as expected. Could be tested in LO by Tools → SQL with direct connected MariaDB.

#11

You’re right! StackOverflow would be a much better place for a SQL question, but they have much stricter expectations for questions, and my privileges for posting questions there have been suspended, and I can’t find a way to get them back. Pity, because I would know exactly how to post this issue that would fit their rules easily.

#12

@Ratslinger, thanks! SQLFiddle is using MySQL 5.6, so this must be something the newer versions are equipped to handle, but the older versions were not.

@RobertG, thanks! Yes, the MySQL 8.0 reference manual @Ratslinger posted states that

Prior to MySQL 8.0.16, CREATE TABLE permits only the following limited version of table CHECK constraint syntax, which is parsed and ignored

That would explain why you had to add the constraint after creating the table. Out of curiosity, when you got the constraint to work in 8.0, what would the result of show CREATE TABLE Persons include the constraint?

#13

I’m running MySQL 5.7.19, and even when I try to add a very simple CHECK constraint (one that involves only one field) to an existing table, it says

QUERY OK

and the constraint is not created. I try to find the constraint with select * from information_schema.table_constraints, and it returns all constraints on all tables, but none are of type “CHECK”. Apparently, the problem is not the complexity of the CHECK constraint I want to add, but the fact that I’m in such an old MySQL version and I’m trying to use a CHECK constraint.

#14

@agerber85 : Show create will show:

CREATE TABLE `Persons` (
  `ID` int(11) NOT NULL,
  `LastName` varchar(255) NOT NULL,
  `FirstName` varchar(255) DEFAULT NULL,
  `Age` int(11) DEFAULT NULL,
  `City` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  CONSTRAINT `CHK_Person` CHECK (`Age` >= 18 or `City` = 'Sandnes' and `LastName` = 'Robert')
) ENGINE=InnoDB DEFAULT CHARSET=latin1

All with MariaDB 10.5.17

#15

I suppose this is an opportunity for me to learn about triggers, which I’m reading here are a way to emulate a check constraint.

#16

@RobertG: Yes, that makes sense, since MariaDB parses and includes check constraints within a CREATE TABLE command. Thanks for testing!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.