maria-discuss team mailing list archive
Mailing list archive
Re: Virtual CHECK constraint using Virtual columns
On Mon, Apr 4, 2016 at 2:10 PM, Peter Laursen <peter_laursen@xxxxxxxxxx>
> As described in this Blog
> 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
I can't answer that, but there's another workaround for (some) CHECK
constraints, described here:
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 (
AS (IF(i BETWEEN 7 AND 12, TRUE, FALSE))
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.