Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns
On Sun, Oct 16, 2016 at 5:15 PM, Sergei Golubchik wrote: > Hi, Peter! > > On Oct 16, Peter Laursen wrote: > > Didn't CHECK CONSTRAINTS get introduced in MySQL 8? Then better port it > > from there into MariaDB I think. -- Peter > > I'm afraid you've got it backwards :) > > MySQL 8.0 has no CHECK constraint (at least it's not mentioned in > http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html). > > MariaDB has it (https://mariadb.com/kb/en/mariadb/constraint/) since July > 4th. > > Regards, > Sergei > Chief Architect MariaDB > and secur...@mariadb.org Sergei, great and thank you! I wasn't paying attention, This is great news (to me)! ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns
As Sergei said, MariaDB 10.2 has CHECKs. In old versions, honestly I don't like your solution. Take a look at what locks will be set: https://www.percona.com/blog/2006/12/12/innodb-locking-and-foreign-keys/ You can use trigger instead. When you only want to execute a check like this, triggers are not slow. Just SIGNAL an error is the value of i_must_be_between_7_and_12 is not valid. Federico Dom 16/10/16, Pantelis Theodosiou ha scritto: Oggetto: Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns A: "Maria Discuss" Data: Domenica 16 ottobre 2016, 16:45 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 wrote: On Mon, Apr 4, 2016 at 2:10 PM, Peter Laursen wrote: 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/ questions/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 -Segue allegato- ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns
ok .. I should have checked properly! :-( On Sun, Oct 16, 2016 at 6:15 PM, Sergei Golubchik wrote: > Hi, Peter! > > On Oct 16, Peter Laursen wrote: > > Didn't CHECK CONSTRAINTS get introduced in MySQL 8? Then better port it > > from there into MariaDB I think. -- Peter > > I'm afraid you've got it backwards :) > > MySQL 8.0 has no CHECK constraint (at least it's not mentioned in > http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html). > > MariaDB has it (https://mariadb.com/kb/en/mariadb/constraint/) since July > 4th. > > Regards, > Sergei > Chief Architect MariaDB > and secur...@mariadb.org > ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns
Hi, Peter! On Oct 16, Peter Laursen wrote: > Didn't CHECK CONSTRAINTS get introduced in MySQL 8? Then better port it > from there into MariaDB I think. -- Peter I'm afraid you've got it backwards :) MySQL 8.0 has no CHECK constraint (at least it's not mentioned in http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html). MariaDB has it (https://mariadb.com/kb/en/mariadb/constraint/) since July 4th. Regards, Sergei Chief Architect MariaDB and secur...@mariadb.org ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns
My suggestion is for documenting existing functionality and a use case for VIRTUAL columns. When CHECK constraints are actually added, it will be obsolete of course but still useful for those that use older versions (5, 10). On Sun, Oct 16, 2016 at 3:51 PM, Peter Laursen wrote: > 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 > 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 >> wrote: >> >>> >>> >>> >>> >> > ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns
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 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 > wrote: > >> >> >> On Mon, Apr 4, 2016 at 2:10 PM, Peter Laursen >> 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@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > > ___ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns
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 wrote: > > > On Mon, Apr 4, 2016 at 2:10 PM, Peter Laursen > wrote: > >> 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/ > questions/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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns
On Mon, Apr 4, 2016 at 2:10 PM, Peter Laursen wrote: > 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/questions/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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
