SQLite

Check-in [55b314a22c]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 55b314a22c69fbe129b024e953f3230b67eaaa87
User & Date: drh 2005-11-03 01:22:31.000
Context
2005-11-03
02:03
Disallow subqueries in CHECK constraints. (CVS 2756) (check-in: db27afc4cd user: drh tags: trunk)
01:22
CHECK constraints that evaluate to NULL pass. (CVS 2755) (check-in: 55b314a22c 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: 2313d912ba user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/insert.c.
︙︙
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

**    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:








|








8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

**    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:
︙︙
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883

  /* 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) */









|








869
870
871
872
873
874
875
876
877
878
879
880
881
882
883

  /* 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. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 2005 November 2
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    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














|








1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 2005 November 2
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    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
︙︙
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

    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








|




|




|




|


>








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

    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
︙︙