test/tools/csql/exp.test123.ksh

   1 echo CREATE TABLE t1(f1 int,f2 int,primary key(f1));
   2 Statement Executed
   3 echo CREATE TABLE t2(f3 int,f4 int,primary key(f4),foreign key(f3) references t1(f1));
   4 Statement Executed
   5 echo CREATE TABLE t3(f5 int,f6 int,foreign key(f5) references t2(f4));
   6 Statement Executed
   7 echo INSERT INTO t1 values(1,10);
   8 Statement Executed: Rows Affected = 1
   9 echo INSERT INTO t1 values(2,20);
  10 Statement Executed: Rows Affected = 1
  11 echo INSERT INTO t2 values(1,100);
  12 Statement Executed: Rows Affected = 1
  13 echo INSERT INTO t2 values(1,200);
  14 Statement Executed: Rows Affected = 1
  15 echo INSERT INTO t2 values(2,300);
  16 Statement Executed: Rows Affected = 1
  17 echo INSERT INTO t3 values(100,1000);
  18 Statement Executed: Rows Affected = 1
  19 echo INSERT INTO t3 values(100,1000);
  20 Statement Executed: Rows Affected = 1
  21 echo INSERT INTO t3 values(200,1000);
  22 Statement Executed: Rows Affected = 1
  23 echo INSERT INTO t3 values(200,1000);
  24 Statement Executed: Rows Affected = 1
  25 echo SELECT * FROM t1;
  26 ---------------------------------------------------------
  27         t1.f1   t1.f2
  28 ---------------------------------------------------------
  29         1       10
  30         2       20
  31
  32 echo SELECT * FROM t2;
  33 ---------------------------------------------------------
  34         t2.f3   t2.f4
  35 ---------------------------------------------------------
  36         1       100
  37         1       200
  38         2       300
  39
  40 echo SELECT * FROM t3;
  41 ---------------------------------------------------------
  42         t3.f5   t3.f6
  43 ---------------------------------------------------------
  44         100     1000
  45         100     1000
  46         200     1000
  47         200     1000
  48
  49 echo INSERT INTO t2 values(3,100);
  50 Statement execute failed with error -35
  51 echo INSERT INTO t3 values(400,1000);
  52 Statement execute failed with error -35
  53 echo SELECT * FROM t1;
  54 ---------------------------------------------------------
  55         t1.f1   t1.f2
  56 ---------------------------------------------------------
  57         1       10
  58         2       20
  59
  60 echo SELECT * FROM t2;
  61 ---------------------------------------------------------
  62         t2.f3   t2.f4
  63 ---------------------------------------------------------
  64         1       100
  65         1       200
  66         2       300
  67
  68 echo SELECT * FROM t3;
  69 ---------------------------------------------------------
  70         t3.f5   t3.f6
  71 ---------------------------------------------------------
  72         100     1000
  73         100     1000
  74         200     1000
  75         200     1000
  76
  77 echo UPDATE t3 SET f5=400 where f5=200;
  78 Statement execute failed with error -35
  79 echo UPDATE t1 SET f1=3 where f1=2;
  80 Statement prepare failed with error -21
  81 echo UPDATE t2 SET f4=400 where f5=200;
  82 Statement prepare failed with error -21
  83 echo UPDATE t3 SET f5=300 where f5=200;
  84 Statement Executed: Rows Affected = 2
  85 echo SELECT * FROM t1;
  86 ---------------------------------------------------------
  87         t1.f1   t1.f2
  88 ---------------------------------------------------------
  89         1       10
  90         2       20
  91
  92 echo SELECT * FROM t2;
  93 ---------------------------------------------------------
  94         t2.f3   t2.f4
  95 ---------------------------------------------------------
  96         1       100
  97         1       200
  98         2       300
  99
 100 echo SELECT * FROM t3;
 101 ---------------------------------------------------------
 102         t3.f5   t3.f6
 103 ---------------------------------------------------------
 104         100     1000
 105         100     1000
 106         300     1000
 107         300     1000
 108
 109 echo DELETE FROM t1 WHERE f1=2;
 110 Statement execute failed with error -36
 111 echo DELETE FROM t2 where f4=200;
 112 Statement Executed: Rows Affected = 1
 113 echo DELETE FROM t3 WHERE f5=300;
 114 Statement Executed: Rows Affected = 2
 115 echo SELECT * FROM t1;
 116 ---------------------------------------------------------
 117         t1.f1   t1.f2
 118 ---------------------------------------------------------
 119         1       10
 120         2       20
 121
 122 echo SELECT * FROM t2;
 123 ---------------------------------------------------------
 124         t2.f3   t2.f4
 125 ---------------------------------------------------------
 126         1       100
 127         2       300
 128
 129 echo SELECT * FROM t3;
 130 ---------------------------------------------------------
 131         t3.f5   t3.f6
 132 ---------------------------------------------------------
 133         100     1000
 134         100     1000
 135
 136 echo DELETE FROM t2 where f4 IN(200,300,400);
 137 Statement Executed: Rows Affected = 1
 138 echo DELETE FROM t1 WHERE f1=2;
 139 Statement Executed: Rows Affected = 1
 140 echo SELECT * FROM t1;
 141 ---------------------------------------------------------
 142         t1.f1   t1.f2
 143 ---------------------------------------------------------
 144         1       10
 145
 146 echo SELECT * FROM t2;
 147 ---------------------------------------------------------
 148         t2.f3   t2.f4
 149 ---------------------------------------------------------
 150         1       100
 151
 152 echo SELECT * FROM t3;
 153 ---------------------------------------------------------
 154         t3.f5   t3.f6
 155 ---------------------------------------------------------
 156         100     1000
 157         100     1000
 158
 159 Statement Executed
 160 Statement Executed
 161 Statement Executed