maria-discuss team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

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 <ypercube@xxxxxxxxx>
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 <ypercube@xxxxxxxxx>
> wrote:
>
>>
>>
>> On Mon, Apr 4, 2016 at 2:10 PM, Peter Laursen <peter_laursen@xxxxxxxxxx>
>> 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     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>