Home | History | Annotate | Download | only in test 
      1 # 2001 September 15
      2 #
      3 # The author disclaims copyright to this source code.  In place of
      4 # a legal notice, here is a blessing:
      5 #
      6 #    May you do good and not evil.
      7 #    May you find forgiveness for yourself and forgive others.
      8 #    May you share freely, never taking more than you give.
      9 #
     10 #***********************************************************************
     11 # This file implements regression tests for SQLite library.
     12 #
     13 # This file implements tests for foreign keys.
     14 #
     15 
     16 set testdir [file dirname $argv0]
     17 source $testdir/tester.tcl
     18 
     19 ifcapable {!foreignkey} {
     20   finish_test
     21   return
     22 }
     23 
     24 # Create a table and some data to work with.
     25 #
     26 do_test fkey1-1.0 {
     27   execsql {
     28     CREATE TABLE t1(
     29       a INTEGER PRIMARY KEY,
     30       b INTEGER
     31            REFERENCES t1 ON DELETE CASCADE
     32            REFERENCES t2,
     33       c TEXT,
     34       FOREIGN KEY (b,c) REFERENCES t2(x,y) ON UPDATE CASCADE
     35     );
     36   }
     37 } {}
     38 do_test fkey1-1.1 {
     39   execsql {
     40     CREATE TABLE t2(
     41       x INTEGER PRIMARY KEY,
     42       y TEXT
     43     );
     44   }
     45 } {}
     46 do_test fkey1-1.2 {
     47   execsql {
     48     CREATE TABLE t3(
     49       a INTEGER REFERENCES t2,
     50       b INTEGER REFERENCES t1,
     51       FOREIGN KEY (a,b) REFERENCES t2(x,y)
     52     );
     53   }
     54 } {}
     55 
     56 do_test fkey1-2.1 {
     57   execsql {
     58     CREATE TABLE t4(a integer primary key);
     59     CREATE TABLE t5(x references t4);
     60     CREATE TABLE t6(x references t4);
     61     CREATE TABLE t7(x references t4);
     62     CREATE TABLE t8(x references t4);
     63     CREATE TABLE t9(x references t4);
     64     CREATE TABLE t10(x references t4);
     65     DROP TABLE t7;
     66     DROP TABLE t9;
     67     DROP TABLE t5;
     68     DROP TABLE t8;
     69     DROP TABLE t6;
     70     DROP TABLE t10;
     71   }
     72 } {}
     73 
     74 do_test fkey1-3.1 {
     75   execsql {
     76     CREATE TABLE t5(a PRIMARY KEY, b, c);
     77     CREATE TABLE t6(
     78       d REFERENCES t5,
     79       e REFERENCES t5(c)
     80     );
     81     PRAGMA foreign_key_list(t6);
     82   }
     83 } [concat                                         \
     84   {0 0 t5 e c {NO ACTION} {NO ACTION} NONE}       \
     85   {1 0 t5 d {} {NO ACTION} {NO ACTION} NONE}      \
     86 ]
     87 do_test fkey1-3.2 {
     88   execsql {
     89     CREATE TABLE t7(d, e, f,
     90       FOREIGN KEY (d, e) REFERENCES t5(a, b)
     91     );
     92     PRAGMA foreign_key_list(t7);
     93   }
     94 } [concat                                   \
     95   {0 0 t5 d a {NO ACTION} {NO ACTION} NONE} \
     96   {0 1 t5 e b {NO ACTION} {NO ACTION} NONE} \
     97 ]
     98 do_test fkey1-3.3 {
     99   execsql {
    100     CREATE TABLE t8(d, e, f,
    101       FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL
    102     );
    103     PRAGMA foreign_key_list(t8);
    104   }
    105 } [concat                        \
    106   {0 0 t5 d {} {SET NULL} CASCADE NONE} \
    107   {0 1 t5 e {} {SET NULL} CASCADE NONE} \
    108 ]
    109 do_test fkey1-3.4 {
    110   execsql {
    111     CREATE TABLE t9(d, e, f,
    112       FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET DEFAULT
    113     );
    114     PRAGMA foreign_key_list(t9);
    115   }
    116 } [concat                        \
    117   {0 0 t5 d {} {SET DEFAULT} CASCADE NONE} \
    118   {0 1 t5 e {} {SET DEFAULT} CASCADE NONE} \
    119 ]
    120 
    121 finish_test
    122