/ Check-in [55b314a2]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:CHECK constraints that evaluate to NULL pass. (CVS 2755)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 55b314a22c69fbe129b024e953f3230b67eaaa87
User & Date: drh 2005-11-03 01:22:31
Context
2005-11-03
02:03
Disallow subqueries in CHECK constraints. (CVS 2756) check-in: db27afc4 user: drh tags: trunk
01:22
CHECK constraints that evaluate to NULL pass. (CVS 2755) check-in: 55b314a2 user: drh tags: trunk
00:41
First cut at supporting CHECK constraints. Everything appears to work, but much more testing is needed as well as documentation. (CVS 2754) check-in: 2313d912 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.145 2005/11/03 00:41:17 drh Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:
................................................................................
  /* Test all CHECK constraints
  */
#ifndef SQLITE_OMIT_CHECK
  if( pTab->pCheck ){
    int allOk = sqlite3VdbeMakeLabel(v);
    assert( pParse->ckOffset==0 );
    pParse->ckOffset = nCol;
    sqlite3ExprIfTrue(pParse, pTab->pCheck, allOk, 0);
    assert( pParse->ckOffset==nCol );
    pParse->ckOffset = 0;
    sqlite3VdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, OE_Abort);
    sqlite3VdbeResolveLabel(v, allOk);
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */








|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.146 2005/11/03 01:22:31 drh Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:
................................................................................
  /* Test all CHECK constraints
  */
#ifndef SQLITE_OMIT_CHECK
  if( pTab->pCheck ){
    int allOk = sqlite3VdbeMakeLabel(v);
    assert( pParse->ckOffset==0 );
    pParse->ckOffset = nCol;
    sqlite3ExprIfTrue(pParse, pTab->pCheck, allOk, 1);
    assert( pParse->ckOffset==nCol );
    pParse->ckOffset = 0;
    sqlite3VdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, OE_Abort);
    sqlite3VdbeResolveLabel(v, allOk);
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

Changes to test/check.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

81
82
83
84
85
86
87
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing CHECK constraints
#
# $Id: check.test,v 1.1 2005/11/03 00:41:18 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Only run these tests if the build includes support for CHECK constraints
ifcapable !check {
  finish_test
................................................................................
    SELECT * FROM t1;
  }  
} {3 4}
do_test check-1.7 {
  catchsql {
    INSERT INTO t1 VALUES(NULL,6);
  }
} {1 {constraint failed}}
do_test check-1.8 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4}
do_test check-1.9 {
  catchsql {
    INSERT INTO t1 VALUES(2,NULL);
  }
} {1 {constraint failed}}
do_test check-1.10 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4}
do_test check-1.11 {
  execsql {

    UPDATE t1 SET x=2 WHERE x==3;
    SELECT * FROM t1;
  }
} {2 4}
do_test check-1.12 {
  catchsql {
    UPDATE t1 SET x=7 WHERE x==2







|







 







|




|




|




|


>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing CHECK constraints
#
# $Id: check.test,v 1.2 2005/11/03 01:22:31 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Only run these tests if the build includes support for CHECK constraints
ifcapable !check {
  finish_test
................................................................................
    SELECT * FROM t1;
  }  
} {3 4}
do_test check-1.7 {
  catchsql {
    INSERT INTO t1 VALUES(NULL,6);
  }
} {0 {}}
do_test check-1.8 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4 {} 6}
do_test check-1.9 {
  catchsql {
    INSERT INTO t1 VALUES(2,NULL);
  }
} {0 {}}
do_test check-1.10 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4 {} 6 2 {}}
do_test check-1.11 {
  execsql {
    DELETE FROM t1 WHERE x IS NULL OR x!=3;
    UPDATE t1 SET x=2 WHERE x==3;
    SELECT * FROM t1;
  }
} {2 4}
do_test check-1.12 {
  catchsql {
    UPDATE t1 SET x=7 WHERE x==2