github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/foreign_key/fk_self_refer2.sql (about) 1 drop database if exists fk_self_refer2; 2 create database fk_self_refer2; 3 use fk_self_refer2; 4 5 drop table if exists t1; 6 ----create two FKs on a primary key---- 7 -- test only two FKs self refer in a table 8 create table t1(a int primary key,b int,c int, 9 constraint `c1` foreign key fk1(b) references t1(a), 10 constraint `c2` foreign key fk2(c) references t1(a) 11 ); 12 show create table t1; 13 14 --should be error. 2,3 does no exists 15 insert into t1 values (1,2,3); 16 17 --no error 18 insert into t1 values (1,1,1); 19 20 --no error 21 insert into t1 values (2,2,1); 22 23 --no error 24 insert into t1 values (3,3,2); 25 26 --no error 27 insert into t1 values (4,3,1); 28 29 --error 30 insert into t1 values (5,6,1); 31 32 --no error 33 insert into t1 values (6,NULL,1); 34 35 --error 36 insert into t1 values (7,NULL,8); 37 38 --no error 39 insert into t1 values (8,NULL,NULL); 40 41 --error 42 delete from t1 where a = 1; 43 44 --error 45 delete from t1 where a = 2; 46 47 --error 48 delete from t1 where a = 3; 49 50 --no error 51 delete from t1 where a = 4; 52 53 --no error 54 delete from t1 where a = 6; 55 56 update t1 set b = 8 where a= 3; 57 58 --error 59 delete from t1 where a = 8; 60 61 --no error 62 delete from t1 where a = 3; 63 64 --no error 65 delete from t1 where a = 8; 66 67 -- error 68 update t1 set a = 3 where a = 2; 69 70 --no error 71 update t1 set b = NULL where a = 2; 72 73 --no error 74 delete from t1 where a= 2; 75 76 --no error 77 update t1 set b = NULL where a = 1; 78 79 --error 80 delete from t1 where a = 1; 81 82 --no error 83 update t1 set c = NULL where a = 1; 84 85 --no error 86 delete from t1 where a = 1; 87 88 --0 89 select count(*) from t1; 90 91 92 drop table if exists t1; 93 ----create two FKs on a unique key---- 94 -- test only two FKs self refer in a table 95 create table t1(a int unique key,b int,c int, 96 constraint `c1` foreign key fk1(b) references t1(a), 97 constraint `c2` foreign key fk2(c) references t1(a) 98 ); 99 show create table t1; 100 101 --no error 102 insert into t1 values (NULL,NULL,NULL); 103 104 --no error 105 insert into t1 values (1,1,1); 106 107 --no error 108 insert into t1 values (2,1,1); 109 110 --no error. 111 update t1 set a = NULL where a = 2; 112 113 --error 114 delete from t1 where c = 1; 115 116 --should be error. 1 duplicate 117 --insert into t1 values (1,2,3); 118 119 --no error 120 insert into t1 values (2,2,1); 121 122 --no error 123 insert into t1 values (3,3,2); 124 125 --no error 126 insert into t1 values (4,3,1); 127 128 --error. 6 does not exists 129 insert into t1 values (5,6,1); 130 131 --no error 132 insert into t1 values (6,NULL,1); 133 134 --error. 8 does not exists 135 insert into t1 values (7,NULL,8); 136 137 --no error 138 insert into t1 values (8,NULL,NULL); 139 140 --error 141 delete from t1 where a = 1; 142 143 --error 144 delete from t1 where a = 2; 145 146 --error 147 delete from t1 where a = 3; 148 149 --no error 150 delete from t1 where a = 4; 151 152 --no error 153 delete from t1 where a = 6; 154 155 update t1 set b = 8 where a = 3; 156 157 --error. 6 does not exist 158 update t1 set b = 6 where a = 3; 159 160 --error 161 delete from t1 where a = 8; 162 163 --no error 164 delete from t1 where a = 3; 165 166 --no error 167 delete from t1 where a = 8; 168 169 -- error 170 update t1 set a = 3 where a = 2; 171 172 --no error 173 update t1 set b = NULL where a = 2; 174 175 --no error 176 update t1 set a = 3 where a = 2; 177 178 --no error 179 delete from t1 where a = 2; 180 181 delete from t1 where a = 3; 182 183 --no error 184 update t1 set b = NULL where a = 1; 185 186 --error 187 delete from t1 where a = 1; 188 189 --no error 190 update t1 set c = NULL where a = 1; 191 192 --no error 193 delete from t1 where a = 1; 194 195 update t1 set b = null,c = NULL where a is null; 196 197 delete from t1 where a = 1; 198 199 delete from t1 where a is null; 200 201 --0 202 select count(*) from t1; 203 204 205 drop table if exists t1; 206 ----create two FKs on a unique key and a primary key---- 207 -- test only two FKs self refer in a table 208 create table t1(a int primary key, 209 b int unique key, 210 c int, 211 constraint `c1` foreign key fk1(c) references t1(a), 212 constraint `c2` foreign key fk2(c) references t1(b) 213 ); 214 show create table t1; 215 216 --no error 217 insert into t1 values (1,1,1); 218 219 --error 220 insert into t1 values (2,2,3); 221 222 --error. 3 does not exist in the column b 223 insert into t1 values (3,2,3); 224 225 --no error 226 insert into t1 values (2,2,NULL); 227 228 --no error 229 insert into t1 values (3,3,2); 230 231 --error. 4 doest not exist in the column b 232 insert into t1 values (4,5,4); 233 234 --no error 235 insert into t1 values (4,5,NULL); 236 237 --no error 238 insert into t1 values (5,6,5); 239 240 --error 241 delete from t1 where a = 4; 242 243 --error. 7 does not exist in the column a 244 insert into t1 values (8,7,7); 245 246 --error 247 delete from t1 where a= 2; 248 249 --error 250 update t1 set b = NULL where a = 2; 251 252 --no error 253 delete from t1 where a = 3; 254 255 --no error 256 delete from t1 where a = 2; 257 258 --error 259 delete from t1 where a = 4; 260 261 --error 262 delete from t1 where a = 5; 263 264 --no error 265 update t1 set c = 1 where a = 5; 266 267 --no error 268 delete from t1 where a = 5; 269 270 --no error 271 delete from t1 where a = 4; 272 273 --error 274 delete from t1 where a = 1; 275 276 --error 277 update t1 set b = NULL where a = 1; 278 279 --error 280 update t1 set b = 2 where a = 1; 281 282 --no error 283 update t1 set c = NULL where a = 1; 284 285 --no error 286 update t1 set b = 2 where a = 1; 287 288 --no error 289 delete from t1 where a = 1; 290 291 --no error 292 select count(*) from t1; 293 294 drop table if exists t1; 295 drop database if exists fk_self_refer2;