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
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 (
AS (i BETWEEN 7 AND 12) -- whatever CHECK
constraint we want here
FOREIGN KEY (i_must_be_between_7_and_12)
REFERENCES truth (t)
On Wed, Apr 6, 2016 at 6:46 PM, Pantelis Theodosiou <yperc...@gmail.com>
> On Mon, Apr 4, 2016 at 2:10 PM, Peter Laursen <peter_laur...@webyog.com>
>> 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/
> 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))
> 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.
Mailing list: https://launchpad.net/~maria-discuss
Post to : email@example.com
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp