Didn't CHECK CONSTRAINTS get introduced in MySQL 8? Then better port it from there into MariaDB I think. -- Peter
On Sun, Oct 16, 2016 at 4:45 PM, Pantelis Theodosiou <[email protected]> wrote: > 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 <[email protected]> > wrote: > >> >> >> On Mon, Apr 4, 2016 at 2:10 PM, Peter Laursen <[email protected]> >> wrote: >> >>> As described in this Blog http://mablomy.blogspot.d >>> k/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/q >> uestions/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 : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > >
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
