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

_______________________________________________
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