As Sergei said, MariaDB 10.2 has CHECKs. In old versions, honestly I don't like your solution. Take a look at what locks will be set: https://www.percona.com/blog/2006/12/12/innodb-locking-and-foreign-keys/ You can use trigger instead. When you only want to execute a check like this, triggers are not slow. Just SIGNAL an error is the value of i_must_be_between_7_and_12 is not valid. Federico -------------------------------------------- Dom 16/10/16, Pantelis Theodosiou <ypercube@gmail.com> ha scritto: Oggetto: Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns A: "Maria Discuss" <maria-discuss@lists.launchpad.net> Data: Domenica 16 ottobre 2016, 16:45 Would this be good to be added in the documentation of VIRTUAL columns? Or as a separate page, as a way to enforce/emulate arbitrary CHECK constraints? It can be slightly simplified (IF is not needed) and the BOOLEAN could be BIT (not sure if that adds any complication): CREATE TABLE truth (t BIT PRIMARY KEY) ; INSERT INTO truth (t) VALUES (TRUE) ; -- and remove all write permissions to the table CREATE TABLE checker ( i float, i_must_be_between_7_and_12 BIT AS (i BETWEEN 7 AND 12) -- whatever CHECK constraint we want here PERSISTENT, CONSTRAINT check_i_must_be_between_7_and_ 12 FOREIGN KEY (i_must_be_between_7_and_12) REFERENCES truth (t) ); On Wed, Apr 6, 2016 at 6:46 PM, Pantelis Theodosiou <ypercube@gmail.com> wrote: On Mon, Apr 4, 2016 at 2:10 PM, Peter Laursen <peter_laursen@webyog.com> wrote: As described in this Blog http://mablomy.blogspot. dk/2016/04/check-constraint- for-mysql-not-null-on.html. A very nice hack/trick IMO. However it is not working with MariaDB as VC's cannot be declared NOT NULL. What prevents that? (Peter, sorry fro the previous private reply, not sure how I got the reply buttons wrong.) I can't answer that, but there's another workaround for (some) CHECK constraints, described here: http://dba.stackexchange.com/ questions/9662/check- constraint-does-not-work/ 22019#22019 Unfortunately, it works only for smallish (int or date) ranges. We can't use for floats or decimals (as it would require a very big reference table). But it could be combined with the hack you link, using something like: CREATE TABLE truth (t BOOLEAN PRIMARY KEY) ; INSERT INTO truth (t) VALUES (TRUE) ; -- and remove all write permissions to the table CREATE TABLE checker ( i int, i_must_be_between_7_and_12 BOOLEAN AS (IF(i BETWEEN 7 AND 12, TRUE, FALSE)) PERSISTENT, CONSTRAINT check_i_must_be_between_7_and_ 12 FOREIGN KEY (i_must_be_between_7_and_12) REFERENCES truth (t) ); Haven't tested it but should work for more complex constraints as well. Pantelis -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp