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.ht.... 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/2... 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