github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/foreign_key/fk_base.result (about) 1 create table f1(b int, a int primary key); 2 create table f2(b int, aa varchar primary key); 3 create table c1 (a int, b int, foreign key f_a(a) references f1(c)); 4 internal error: column 'c' no exists in table 'f1' 5 create table c1 (a int, b int, foreign key f_a(a) references f1(b)); 6 internal error: failed to add the foreign key constraint 7 create table c1 (a int, b int, foreign key f_a(a) references f2(aa)); 8 internal error: type of reference column 'aa' is not match for column 'a' 9 create table c1 (a int, b int, foreign key f_a(a) references f1(a)); 10 select * from f1; 11 a b 12 select * from c1; 13 a b 14 drop table f1; 15 internal error: can not drop table 'f1' referenced by some foreign key constraint 16 truncate f1; 17 internal error: can not truncate table 'f1' referenced by some foreign key constraint 18 truncate c1; 19 drop table f1; 20 internal error: can not drop table 'f1' referenced by some foreign key constraint 21 drop table c1; 22 drop table f1; 23 drop table f2; 24 create table f1(a int primary key, b int unique key); 25 create table c1 (a int, b int, foreign key f_a(a) references f1(a)); 26 insert into f1 values (1,1), (2,2), (3,3); 27 insert into c1 values (1,1), (2,2), (3,3); 28 delete from f1 where a > 1; 29 internal error: Cannot delete or update a parent row: a foreign key constraint fails 30 drop table c1; 31 create table c1 (a int, b int, foreign key f_a(a) references f1(a) on delete set null); 32 insert into c1 values (1,1), (2,2), (3,3); 33 delete from f1 where a > 1; 34 select * from c1; 35 a b 36 1 1 37 NULL 2 38 NULL 3 39 drop table c1; 40 insert into f1 values (2,2), (3,3); 41 create table c1 (a int, b int, foreign key f_a(a) references f1(a) on delete cascade); 42 insert into c1 values (1,1), (2,2), (3,3); 43 delete from f1 where a > 1; 44 select * from c1; 45 a b 46 1 1 47 drop table c1; 48 drop table f1; 49 create table f1(a int primary key, b int unique key); 50 create table c1 (a int, b int, foreign key f_a(a) references f1(a)); 51 insert into f1 values (1,1), (2,2), (3,3); 52 insert into c1 values (1,1), (2,2), (3,3); 53 update f1 set a = 11 where b = 1; 54 internal error: Cannot delete or update a parent row: a foreign key constraint fails 55 update c1 set a = 11 where b = 1; 56 internal error: Cannot add or update a child row: a foreign key constraint fails 57 update c1 set a = null where b = 1; 58 select * from c1 order by b; 59 a b 60 NULL 1 61 2 2 62 3 3 63 update c1 set a = 3 where b = 2; 64 select * from c1 order by b; 65 a b 66 NULL 1 67 3 2 68 3 3 69 drop table c1; 70 create table c1 (a int, b int, foreign key f_a(a) references f1(a) on update set null); 71 insert into c1 values (1,1), (2,2), (3,3); 72 update f1 set a=11 where a=1; 73 select * from c1 order by b; 74 a b 75 NULL 1 76 2 2 77 3 3 78 drop table c1; 79 drop table f1; 80 create table f1(a int primary key, b int unique key); 81 create table c1 (a int, b int, foreign key f_a(a) references f1(a) on update cascade); 82 insert into f1 values (1,1), (2,2), (3,3); 83 insert into c1 values (1,1), (2,2), (3,3); 84 update f1 set a=0 where b>1; 85 Duplicate entry '0' for key 'a' 86 update f1 set a=0 where b=1; 87 select * from c1 order by b; 88 a b 89 0 1 90 2 2 91 3 3 92 drop table c1; 93 drop table f1; 94 drop table if exists t_dept; 95 CREATE TABLE t_dept 96 ( 97 id INT(11) PRIMARY KEY, 98 name VARCHAR(22) NOT NULL, 99 location VARCHAR(50) 100 ); 101 INSERT INTO t_dept VALUES (10,'ACCOUNTING','NEW YORK'); 102 INSERT INTO t_dept VALUES (20,'RESEARCH','DALLAS'); 103 INSERT INTO t_dept VALUES (30,'SALES','CHICAGO'); 104 INSERT INTO t_dept VALUES (40,'OPERATIONS','BOSTON'); 105 drop table if exists t_emp; 106 CREATE TABLE t_emp 107 ( 108 id INT(11) PRIMARY KEY, 109 name VARCHAR(25), 110 deptId INT(11), 111 salary FLOAT, 112 CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES t_dept(id) 113 ); 114 INSERT INTO t_emp VALUES (7369,'SMITH',20,1300.00); 115 INSERT INTO t_emp VALUES (7499,'ALLEN',30,1600.00); 116 INSERT INTO t_emp VALUES (7521,'WARD ',30,1250.00); 117 INSERT INTO t_emp VALUES (7566,'JONES',20,3475.00); 118 INSERT INTO t_emp VALUES (1234,'MEIXI',30,1250.00); 119 INSERT INTO t_emp VALUES (7698,'BLAKE',30,2850.00); 120 INSERT INTO t_emp VALUES (7782,'CLARK',10,2950.00); 121 INSERT INTO t_emp VALUES (7788,'SCOTT',20,3500.00); 122 update t_dept set id = 50 where name = 'ACCOUNTING'; 123 internal error: Cannot delete or update a parent row: a foreign key constraint fails 124 delete from t_dept where name = 'ACCOUNTING'; 125 internal error: Cannot delete or update a parent row: a foreign key constraint fails 126 update t_emp set deptId = 50 where salary < 1500; 127 internal error: Cannot add or update a child row: a foreign key constraint fails 128 update t_emp set deptId = null where salary < 1500; 129 select * from t_emp order by salary; 130 id name deptid salary 131 7521 WARD null 1250.0 132 1234 MEIXI null 1250.0 133 7369 SMITH null 1300.0 134 7499 ALLEN 30 1600.0 135 7698 BLAKE 30 2850.0 136 7782 CLARK 10 2950.0 137 7566 JONES 20 3475.0 138 7788 SCOTT 20 3500.0 139 select * from t_dept; 140 id name location 141 10 ACCOUNTING NEW YORK 142 20 RESEARCH DALLAS 143 30 SALES CHICAGO 144 40 OPERATIONS BOSTON 145 drop table t_emp; 146 drop table t_dept; 147 drop table if exists t_dept1; 148 CREATE TABLE t_dept1 149 ( 150 id INT(11) PRIMARY KEY, 151 name VARCHAR(22) NOT NULL, 152 location VARCHAR(50) 153 ); 154 INSERT INTO t_dept1 VALUES (10,'ACCOUNTING','NEW YORK'); 155 INSERT INTO t_dept1 VALUES (20,'RESEARCH','DALLAS'); 156 INSERT INTO t_dept1 VALUES (30,'SALES','CHICAGO'); 157 INSERT INTO t_dept1 VALUES (40,'OPERATIONS','BOSTON'); 158 drop table if exists t_emp1; 159 CREATE TABLE t_emp1 160 ( 161 id INT(11) PRIMARY KEY, 162 name VARCHAR(25), 163 deptId INT(11), 164 salary FLOAT, 165 CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES t_dept1(id) ON DELETE CASCADE ON UPDATE CASCADE 166 ); 167 INSERT INTO t_emp1 VALUES (7369,'SMITH',20,1300.00); 168 INSERT INTO t_emp1 VALUES (7499,'ALLEN',30,1600.00); 169 INSERT INTO t_emp1 VALUES (7521,'WARD ',30,1250.00); 170 INSERT INTO t_emp1 VALUES (7566,'JONES',20,3475.00); 171 INSERT INTO t_emp1 VALUES (1234,'MEIXI',30,1250.00); 172 INSERT INTO t_emp1 VALUES (7698,'BLAKE',30,2850.00); 173 INSERT INTO t_emp1 VALUES (7782,'CLARK',10,2950.00); 174 INSERT INTO t_emp1 VALUES (7788,'SCOTT',20,3500.00); 175 update t_dept1 set id = 50 where name = 'ACCOUNTING'; 176 select * from t_dept1; 177 id name location 178 20 RESEARCH DALLAS 179 30 SALES CHICAGO 180 40 OPERATIONS BOSTON 181 50 ACCOUNTING NEW YORK 182 select * from t_emp1; 183 id name deptid salary 184 7369 SMITH 20 1300.0 185 7499 ALLEN 30 1600.0 186 7521 WARD 30 1250.0 187 7566 JONES 20 3475.0 188 1234 MEIXI 30 1250.0 189 7698 BLAKE 30 2850.0 190 7788 SCOTT 20 3500.0 191 7782 CLARK 50 2950.0 192 delete from t_dept1 where name = 'ACCOUNTING'; 193 select * from t_dept1; 194 id name location 195 20 RESEARCH DALLAS 196 30 SALES CHICAGO 197 40 OPERATIONS BOSTON 198 select * from t_emp1; 199 id name deptid salary 200 7369 SMITH 20 1300.0 201 7499 ALLEN 30 1600.0 202 7521 WARD 30 1250.0 203 7566 JONES 20 3475.0 204 1234 MEIXI 30 1250.0 205 7698 BLAKE 30 2850.0 206 7788 SCOTT 20 3500.0 207 update t_emp1 set deptId = 50 where salary < 1500; 208 internal error: Cannot add or update a child row: a foreign key constraint fails 209 update t_emp1 set deptId = null where salary < 1500; 210 drop table t_emp1; 211 drop table t_dept1; 212 drop table if exists t_dept2; 213 CREATE TABLE t_dept2 214 ( 215 id INT(11) PRIMARY KEY, 216 name VARCHAR(22) NOT NULL, 217 location VARCHAR(50) 218 ); 219 INSERT INTO t_dept2 VALUES (10,'ACCOUNTING','NEW YORK'); 220 INSERT INTO t_dept2 VALUES (20,'RESEARCH','DALLAS'); 221 INSERT INTO t_dept2 VALUES (30,'SALES','CHICAGO'); 222 INSERT INTO t_dept2 VALUES (40,'OPERATIONS','BOSTON'); 223 drop table if exists t_emp2; 224 CREATE TABLE t_emp2 225 ( 226 id INT(11) PRIMARY KEY, 227 name VARCHAR(25), 228 deptId INT(11), 229 salary FLOAT, 230 CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES t_dept2(id) ON DELETE SET NULL ON UPDATE SET NULL 231 ); 232 INSERT INTO t_emp2 VALUES (7369,'SMITH',20,1300.00); 233 INSERT INTO t_emp2 VALUES (7499,'ALLEN',30,1600.00); 234 INSERT INTO t_emp2 VALUES (7521,'WARD ',30,1250.00); 235 INSERT INTO t_emp2 VALUES (7566,'JONES',20,3475.00); 236 INSERT INTO t_emp2 VALUES (1234,'MEIXI',30,1250.00); 237 INSERT INTO t_emp2 VALUES (7698,'BLAKE',30,2850.00); 238 INSERT INTO t_emp2 VALUES (7782,'CLARK',10,2950.00); 239 INSERT INTO t_emp2 VALUES (7788,'SCOTT',20,3500.00); 240 update t_dept2 set id = 50 where name = 'ACCOUNTING'; 241 select * from t_dept2; 242 id name location 243 20 RESEARCH DALLAS 244 30 SALES CHICAGO 245 40 OPERATIONS BOSTON 246 50 ACCOUNTING NEW YORK 247 select * from t_emp2; 248 id name deptid salary 249 7369 SMITH 20 1300.0 250 7499 ALLEN 30 1600.0 251 7521 WARD 30 1250.0 252 7566 JONES 20 3475.0 253 1234 MEIXI 30 1250.0 254 7698 BLAKE 30 2850.0 255 7788 SCOTT 20 3500.0 256 7782 CLARK null 2950.0 257 delete from t_dept2 where name = 'ACCOUNTING'; 258 select * from t_dept2; 259 id name location 260 20 RESEARCH DALLAS 261 30 SALES CHICAGO 262 40 OPERATIONS BOSTON 263 select * from t_emp2; 264 id name deptid salary 265 7369 SMITH 20 1300.0 266 7499 ALLEN 30 1600.0 267 7521 WARD 30 1250.0 268 7566 JONES 20 3475.0 269 1234 MEIXI 30 1250.0 270 7698 BLAKE 30 2850.0 271 7788 SCOTT 20 3500.0 272 7782 CLARK null 2950.0 273 update t_emp2 set deptId = 50 where salary < 1500; 274 internal error: Cannot add or update a child row: a foreign key constraint fails 275 update t_emp2 set deptId = null where salary < 1500; 276 drop table t_emp2; 277 drop table t_dept2; 278 create table f1(a int primary key, b int unique key); 279 create table f2(aa int primary key, bb int unique key); 280 create table c1 (aaa int, bbb int, foreign key f_a(aaa) references f1(a), foreign key f_b(bbb) references f2(aa)); 281 insert into f1 values (1,1), (2,2), (3,3); 282 insert into f2 values (11,11), (22,22), (33,33); 283 insert into c1 values (1,11), (2,22), (3,33); 284 update c1 set aaa=2, bbb=12 where bbb=11; 285 internal error: Cannot add or update a child row: a foreign key constraint fails 286 update c1 set aaa=4, bbb=22 where bbb=11; 287 internal error: Cannot add or update a child row: a foreign key constraint fails 288 update c1 set aaa=2, bbb=33 where bbb=11; 289 select * from c1 order by bbb; 290 aaa bbbb 291 2 22 292 3 33 293 2 33 294 drop table c1; 295 drop table f2; 296 drop table f1; 297 create table f1(a int primary key, b int unique key); 298 create table c1 (a int, b int, foreign key f_a(a) references f1(a)); 299 insert into f1 values (1,1), (2,2), (3,3); 300 insert into c1 values (11,11); 301 internal error: Cannot add or update a child row: a foreign key constraint fails 302 insert into c1 values (1,1),(11,11); 303 internal error: Cannot add or update a child row: a foreign key constraint fails 304 insert into c1 values (1,1); 305 drop table c1; 306 drop table f1; 307 create table f1(b int, a int primary key); 308 create table c1( a int primary key, b int unique key, c int not null, d int,foreign key(d) references f1(a)); 309 insert into f1 values (1,1), (2,2), (3,3); 310 insert into c1 values(1,2,1,1); 311 insert into c1 values(2,2,1,1); 312 Duplicate entry '2' for key '__mo_index_idx_col' 313 drop table c1; 314 drop table f1; 315 create table fk_01(a int,b varchar(20),c tinyint,primary key(a,b)); 316 create table fk_02(col1 int,col2 varchar(25),col3 tinyint,constraint ck foreign key(col1,col2) REFERENCES fk_01(a,b) on delete RESTRICT on update RESTRICT); 317 create table fk_03(col1 int,col2 varchar(25),col3 tinyint,constraint ck foreign key(col1) REFERENCES fk_01(a) on delete RESTRICT on update RESTRICT); 318 create table fk_04(col1 int,col2 varchar(25),col3 tinyint,constraint ck foreign key(col2) REFERENCES fk_01(b) on delete RESTRICT on update RESTRICT); 319 drop table fk_04; 320 drop table fk_03; 321 drop table fk_02; 322 drop table fk_01; 323 create table f1(a int, b int, primary key (a,b)); 324 create table c1(id int primary key, aa int, bb int, CONSTRAINT `fk_aa` FOREIGN KEY(`aa`) REFERENCES `f1`(`a`)); 325 insert into f1 values (1,1); 326 insert into c1 values (1,1,1); 327 update f1 set a=a; 328 update f1 set a=1; 329 update f1 set a=a, b=2; 330 update f1 set a=2; 331 internal error: Cannot delete or update a parent row: a foreign key constraint fails 332 drop table c1; 333 drop table f1; 334 create table f1(a int, b int, primary key (a,b)); 335 create table c1(id int primary key, aa int, bb int, CONSTRAINT `fk_aa` FOREIGN KEY(`aa`,`bb`) REFERENCES `f1`(`a`,`b`)); 336 insert into f1 values (1,1); 337 insert into c1 values (1,1,1); 338 update f1 set a=a; 339 update f1 set a=a, b=1; 340 update f1 set b=1, a=a; 341 update f1 set a=a, b=2; 342 internal error: Cannot delete or update a parent row: a foreign key constraint fails 343 drop table c1; 344 drop table f1; 345 drop database if exists db1; 346 create database db1; 347 use db1; 348 create table f1(b int, a int primary key); 349 create table t1 (a int, b int); 350 create table t2(b int, a int unique); 351 truncate table f1; 352 drop database db1; 353 create database db1; 354 use db1; 355 show tables; 356 Tables_in_db1 357 drop database db1; 358 create account acc1 ADMIN_NAME 'root' IDENTIFIED BY '123456'; 359 create database db2; 360 use db2; 361 create table f1(b int, a int primary key); 362 create table t1 (a int, b int); 363 create table t2(b int, a int unique); 364 truncate table f1; 365 drop database db2; 366 create database db2; 367 use db2; 368 show tables; 369 Tables_in_db2 370 drop database db2; 371 drop account if exists acc1;