github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/foreign_key/fk_base.sql (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 create table c1 (a int, b int, foreign key f_a(a) references f1(b)); 5 create table c1 (a int, b int, foreign key f_a(a) references f2(aa)); 6 create table c1 (a int, b int, foreign key f_a(a) references f1(a)); 7 select * from f1; 8 select * from c1; 9 drop table f1; 10 truncate f1; 11 truncate c1; 12 drop table f1; 13 drop table c1; 14 drop table f1; 15 drop table f2; 16 17 create table f1(a int primary key, b int unique key); 18 create table c1 (a int, b int, foreign key f_a(a) references f1(a)); 19 insert into f1 values (1,1), (2,2), (3,3); 20 insert into c1 values (1,1), (2,2), (3,3); 21 delete from f1 where a > 1; 22 drop table c1; 23 create table c1 (a int, b int, foreign key f_a(a) references f1(a) on delete set null); 24 insert into c1 values (1,1), (2,2), (3,3); 25 delete from f1 where a > 1; 26 select * from c1; 27 drop table c1; 28 insert into f1 values (2,2), (3,3); 29 create table c1 (a int, b int, foreign key f_a(a) references f1(a) on delete cascade); 30 insert into c1 values (1,1), (2,2), (3,3); 31 delete from f1 where a > 1; 32 select * from c1; 33 drop table c1; 34 drop table f1; 35 36 create table f1(a int primary key, b int unique key); 37 create table c1 (a int, b int, foreign key f_a(a) references f1(a)); 38 insert into f1 values (1,1), (2,2), (3,3); 39 insert into c1 values (1,1), (2,2), (3,3); 40 update f1 set a = 11 where b = 1; 41 update c1 set a = 11 where b = 1; 42 update c1 set a = null where b = 1; 43 select * from c1 order by b; 44 update c1 set a = 3 where b = 2; 45 select * from c1 order by b; 46 drop table c1; 47 create table c1 (a int, b int, foreign key f_a(a) references f1(a) on update set null); 48 insert into c1 values (1,1), (2,2), (3,3); 49 update f1 set a=11 where a=1; 50 select * from c1 order by b; 51 drop table c1; 52 drop table f1; 53 create table f1(a int primary key, b int unique key); 54 create table c1 (a int, b int, foreign key f_a(a) references f1(a) on update cascade); 55 insert into f1 values (1,1), (2,2), (3,3); 56 insert into c1 values (1,1), (2,2), (3,3); 57 update f1 set a=0 where b>1; 58 update f1 set a=0 where b=1; 59 select * from c1 order by b; 60 drop table c1; 61 drop table f1; 62 --2.4 测试外键操作是否正常（on restrict、on casade、 set null三种情况） 63 ------------FOREIGN KEY ON RESTRICT ON UPDATE RESTRICT（默认）---------------- 64 drop table if exists t_dept; 65 CREATE TABLE t_dept 66 ( 67 id INT(11) PRIMARY KEY, 68 name VARCHAR(22) NOT NULL, 69 location VARCHAR(50) 70 ); 71 72 INSERT INTO t_dept VALUES (10,'ACCOUNTING','NEW YORK'); 73 INSERT INTO t_dept VALUES (20,'RESEARCH','DALLAS'); 74 INSERT INTO t_dept VALUES (30,'SALES','CHICAGO'); 75 INSERT INTO t_dept VALUES (40,'OPERATIONS','BOSTON'); 76 77 78 drop table if exists t_emp; 79 CREATE TABLE t_emp 80 ( 81 id INT(11) PRIMARY KEY, 82 name VARCHAR(25), 83 deptId INT(11), 84 salary FLOAT, 85 CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES t_dept(id) 86 ); 87 88 INSERT INTO t_emp VALUES (7369,'SMITH',20,1300.00); 89 INSERT INTO t_emp VALUES (7499,'ALLEN',30,1600.00); 90 INSERT INTO t_emp VALUES (7521,'WARD ',30,1250.00); 91 INSERT INTO t_emp VALUES (7566,'JONES',20,3475.00); 92 INSERT INTO t_emp VALUES (1234,'MEIXI',30,1250.00); 93 INSERT INTO t_emp VALUES (7698,'BLAKE',30,2850.00); 94 INSERT INTO t_emp VALUES (7782,'CLARK',10,2950.00); 95 INSERT INTO t_emp VALUES (7788,'SCOTT',20,3500.00); 96 97 update t_dept set id = 50 where name = 'ACCOUNTING'; 98 delete from t_dept where name = 'ACCOUNTING'; 99 update t_emp set deptId = 50 where salary < 1500; 100 update t_emp set deptId = null where salary < 1500; 101 select * from t_emp order by salary; 102 select * from t_dept; 103 drop table t_emp; 104 drop table t_dept; 105 106 ------------FOREIGN KEY ON DELETE CASCADE ON UPDATE CASCADE---------------- 107 drop table if exists t_dept1; 108 CREATE TABLE t_dept1 109 ( 110 id INT(11) PRIMARY KEY, 111 name VARCHAR(22) NOT NULL, 112 location VARCHAR(50) 113 ); 114 115 INSERT INTO t_dept1 VALUES (10,'ACCOUNTING','NEW YORK'); 116 INSERT INTO t_dept1 VALUES (20,'RESEARCH','DALLAS'); 117 INSERT INTO t_dept1 VALUES (30,'SALES','CHICAGO'); 118 INSERT INTO t_dept1 VALUES (40,'OPERATIONS','BOSTON'); 119 120 121 drop table if exists t_emp1; 122 CREATE TABLE t_emp1 123 ( 124 id INT(11) PRIMARY KEY, 125 name VARCHAR(25), 126 deptId INT(11), 127 salary FLOAT, 128 CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES t_dept1(id) ON DELETE CASCADE ON UPDATE CASCADE 129 ); 130 131 INSERT INTO t_emp1 VALUES (7369,'SMITH',20,1300.00); 132 INSERT INTO t_emp1 VALUES (7499,'ALLEN',30,1600.00); 133 INSERT INTO t_emp1 VALUES (7521,'WARD ',30,1250.00); 134 INSERT INTO t_emp1 VALUES (7566,'JONES',20,3475.00); 135 INSERT INTO t_emp1 VALUES (1234,'MEIXI',30,1250.00); 136 INSERT INTO t_emp1 VALUES (7698,'BLAKE',30,2850.00); 137 INSERT INTO t_emp1 VALUES (7782,'CLARK',10,2950.00); 138 INSERT INTO t_emp1 VALUES (7788,'SCOTT',20,3500.00); 139 140 update t_dept1 set id = 50 where name = 'ACCOUNTING'; 141 select * from t_dept1; 142 select * from t_emp1; 143 144 delete from t_dept1 where name = 'ACCOUNTING'; 145 select * from t_dept1; 146 select * from t_emp1; 147 148 update t_emp1 set deptId = 50 where salary < 1500; 149 update t_emp1 set deptId = null where salary < 1500; 150 151 drop table t_emp1; 152 drop table t_dept1; 153 154 -----------FOREIGN KEY ON DELETE SET NULL ON UPDATE SET NULL------------- 155 drop table if exists t_dept2; 156 CREATE TABLE t_dept2 157 ( 158 id INT(11) PRIMARY KEY, 159 name VARCHAR(22) NOT NULL, 160 location VARCHAR(50) 161 ); 162 163 INSERT INTO t_dept2 VALUES (10,'ACCOUNTING','NEW YORK'); 164 INSERT INTO t_dept2 VALUES (20,'RESEARCH','DALLAS'); 165 INSERT INTO t_dept2 VALUES (30,'SALES','CHICAGO'); 166 INSERT INTO t_dept2 VALUES (40,'OPERATIONS','BOSTON'); 167 168 drop table if exists t_emp2; 169 CREATE TABLE t_emp2 170 ( 171 id INT(11) PRIMARY KEY, 172 name VARCHAR(25), 173 deptId INT(11), 174 salary FLOAT, 175 CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES t_dept2(id) ON DELETE SET NULL ON UPDATE SET NULL 176 ); 177 178 INSERT INTO t_emp2 VALUES (7369,'SMITH',20,1300.00); 179 INSERT INTO t_emp2 VALUES (7499,'ALLEN',30,1600.00); 180 INSERT INTO t_emp2 VALUES (7521,'WARD ',30,1250.00); 181 INSERT INTO t_emp2 VALUES (7566,'JONES',20,3475.00); 182 INSERT INTO t_emp2 VALUES (1234,'MEIXI',30,1250.00); 183 INSERT INTO t_emp2 VALUES (7698,'BLAKE',30,2850.00); 184 INSERT INTO t_emp2 VALUES (7782,'CLARK',10,2950.00); 185 INSERT INTO t_emp2 VALUES (7788,'SCOTT',20,3500.00); 186 187 update t_dept2 set id = 50 where name = 'ACCOUNTING'; 188 select * from t_dept2; 189 select * from t_emp2; 190 191 delete from t_dept2 where name = 'ACCOUNTING'; 192 select * from t_dept2; 193 select * from t_emp2; 194 195 update t_emp2 set deptId = 50 where salary < 1500; 196 update t_emp2 set deptId = null where salary < 1500; 197 198 drop table t_emp2; 199 drop table t_dept2; 200 201 create table f1(a int primary key, b int unique key); 202 create table f2(aa int primary key, bb int unique key); 203 create table c1 (aaa int, bbb int, foreign key f_a(aaa) references f1(a), foreign key f_b(bbb) references f2(aa)); 204 insert into f1 values (1,1), (2,2), (3,3); 205 insert into f2 values (11,11), (22,22), (33,33); 206 insert into c1 values (1,11), (2,22), (3,33); 207 update c1 set aaa=2, bbb=12 where bbb=11; 208 update c1 set aaa=4, bbb=22 where bbb=11; 209 update c1 set aaa=2, bbb=33 where bbb=11; 210 select * from c1 order by bbb; 211 212 drop table c1; 213 drop table f2; 214 drop table f1; 215 create table f1(a int primary key, b int unique key); 216 create table c1 (a int, b int, foreign key f_a(a) references f1(a)); 217 insert into f1 values (1,1), (2,2), (3,3); 218 insert into c1 values (11,11); 219 insert into c1 values (1,1),(11,11); 220 insert into c1 values (1,1); 221 drop table c1; 222 drop table f1; 223 224 create table f1(b int, a int primary key); 225 create table c1( a int primary key, b int unique key, c int not null, d int,foreign key(d) references f1(a)); 226 insert into f1 values (1,1), (2,2), (3,3); 227 insert into c1 values(1,2,1,1); 228 insert into c1 values(2,2,1,1); 229 drop table c1; 230 drop table f1; 231 232 create table fk_01(a int,b varchar(20),c tinyint,primary key(a,b)); 233 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); 234 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); 235 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); 236 drop table fk_04; 237 drop table fk_03; 238 drop table fk_02; 239 drop table fk_01; 240 241 create table f1(a int, b int, primary key (a,b)); 242 create table c1(id int primary key, aa int, bb int, CONSTRAINT `fk_aa` FOREIGN KEY(`aa`) REFERENCES `f1`(`a`)); 243 insert into f1 values (1,1); 244 insert into c1 values (1,1,1); 245 update f1 set a=a; 246 update f1 set a=1; 247 update f1 set a=a, b=2; 248 update f1 set a=2; 249 drop table c1; 250 drop table f1; 251 252 create table f1(a int, b int, primary key (a,b)); 253 create table c1(id int primary key, aa int, bb int, CONSTRAINT `fk_aa` FOREIGN KEY(`aa`,`bb`) REFERENCES `f1`(`a`,`b`)); 254 insert into f1 values (1,1); 255 insert into c1 values (1,1,1); 256 update f1 set a=a; 257 update f1 set a=a, b=1; 258 update f1 set b=1, a=a; 259 update f1 set a=a, b=2; 260 drop table c1; 261 drop table f1; 262 263 drop database if exists db1; 264 create database db1; 265 use db1; 266 create table f1(b int, a int primary key); 267 create table t1 (a int, b int); 268 create table t2(b int, a int unique); 269 truncate table f1; 270 drop database db1; 271 create database db1; 272 use db1; 273 show tables; 274 drop database db1; 275 276 ---------Cross-tenant test--------- 277 create account acc1 ADMIN_NAME 'root' IDENTIFIED BY '123456'; 278 -- @session:id=1&user=acc1:root&password=123456 279 create database db2; 280 use db2; 281 create table f1(b int, a int primary key); 282 create table t1 (a int, b int); 283 create table t2(b int, a int unique); 284 truncate table f1; 285 drop database db2; 286 create database db2; 287 use db2; 288 show tables; 289 drop database db2; 290 -- @session 291 drop account if exists acc1;