maria-discuss team mailing list archive

[Federico Razzoli <federico_raz@xxxxxxxx>]

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 <ypercube@xxxxxxxxx> ha scritto:

 Oggetto: Re: [Maria-discuss] Virtual CHECK constraint using Virtual columns
 A: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
 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 <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.
 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@xxxxxxxxxxxxxxxxxxx
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp