github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/foreign_key/foreign_key_multilayer.sql (about) 1 CREATE TABLE part_fk( 2 P_PARTKEY INTEGER NOT NULL, 3 P_NAME VARCHAR(55) NOT NULL, 4 P_MFGR CHAR(25) NOT NULL, 5 P_BRAND CHAR(10) NOT NULL, 6 P_TYPE VARCHAR(25) NOT NULL, 7 P_SIZE INTEGER NOT NULL, 8 P_CONTAINER CHAR(10) NOT NULL, 9 P_RETAILPRICE DECIMAL(15,2) NOT NULL, 10 P_COMMENT VARCHAR(23) NOT NULL, 11 PRIMARY KEY (P_PARTKEY) 12 ); 13 insert into part_fk values(199,"pink wheat powder burlywood snow","Manufacturer#5","Brand#52","MEDIUM BURNISHED BRASS",49,"LG BOX",2097.99,". special deposits hag"); 14 15 CREATE TABLE region_fk( 16 R_REGIONKEY INTEGER NOT NULL, 17 R_NAME CHAR(25) NOT NULL, 18 R_COMMENT VARCHAR(152), 19 PRIMARY KEY (R_REGIONKEY) 20 ); 21 insert into region_fk values(2,"ASIA","ges. thinly even pinto beans ca"); 22 23 CREATE TABLE NATION_fk( 24 N_NATIONKEY INTEGER NOT NULL, 25 N_NAME CHAR(25) NOT NULL, 26 N_REGIONKEY INTEGER NOT NULL, 27 N_COMMENT VARCHAR(152), 28 PRIMARY KEY (N_NATIONKEY),constraint fk_n foreign key(N_REGIONKEY) REFERENCES region_fk(R_REGIONKEY) 29 ); 30 insert into nation_fk values(13,"VIETNAM",2,"hely enticingly express accounts. even, final"); 31 32 CREATE TABLE supplier_fk( 33 S_SUPPKEY INTEGER NOT NULL, 34 S_NAME CHAR(25) NOT NULL, 35 S_ADDRESS VARCHAR(40) NOT NULL, 36 S_NATIONKEY INTEGER NOT NULL, 37 S_PHONE CHAR(15) NOT NULL, 38 S_ACCTBAL DECIMAL(15,2) NOT NULL, 39 S_COMMENT VARCHAR(101) NOT NULL, 40 PRIMARY KEY (S_SUPPKEY),constraint fk_s foreign key(S_NATIONKEY) REFERENCES nation_fk(N_NATIONKEY) 41 ); 42 insert into supplier_fk values(9991,"Supplier#000009991","RnP1Z uvwftshFtf",13,"23-451-948-8464",6785.10,". furiously pending accounts b"); 43 44 CREATE TABLE PARTSUPP_fk( 45 PS_PARTKEY INTEGER NOT NULL, 46 PS_SUPPKEY INTEGER NOT NULL, 47 PS_AVAILQTY INTEGER NOT NULL, 48 PS_SUPPLYCOST DECIMAL(15,2) NOT NULL, 49 PS_COMMENT VARCHAR(199) NOT NULL, 50 PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY),constraint fk_p1 foreign key(PS_PARTKEY) REFERENCES part_fk(P_PARTKEY),constraint fk_p2 foreign key(PS_SUPPKEY) REFERENCES supplier_fk(S_SUPPKEY) 51 ); 52 insert into PARTSUPP_fk values (199,9991,7872,606.64," according to the final pinto beans: carefully silent requests sleep final"); 53 54 CREATE TABLE customer_fk( 55 C_CUSTKEY INTEGER NOT NULL, 56 C_NAME VARCHAR(25) NOT NULL, 57 C_ADDRESS VARCHAR(40) NOT NULL, 58 C_NATIONKEY INTEGER NOT NULL, 59 C_PHONE CHAR(15) NOT NULL, 60 C_ACCTBAL DECIMAL(15,2) NOT NULL, 61 C_MKTSEGMENT CHAR(10) NOT NULL, 62 C_COMMENT VARCHAR(117) NOT NULL, 63 PRIMARY KEY (C_CUSTKEY),constraint fk_c foreign key(C_NATIONKEY) REFERENCES nation_fk(N_NATIONKEY) 64 ); 65 insert into customer_fk values(12,"Customer#000149992","iwjVf1MZno1",13,"16-684-999-8810",3417.45,"AUTOMOBILE","luffily final requests integrate slyly. furiously special warhorses are furiously alongside o"); 66 67 CREATE TABLE orders_fk( 68 O_ORDERKEY BIGINT NOT NULL, 69 O_CUSTKEY INTEGER NOT NULL, 70 O_ORDERSTATUS CHAR(1) NOT NULL, 71 O_TOTALPRICE DECIMAL(15,2) NOT NULL, 72 O_ORDERDATE DATE NOT NULL, 73 O_ORDERPRIORITY CHAR(15) NOT NULL, 74 O_CLERK CHAR(15) NOT NULL, 75 O_SHIPPRIORITY INTEGER NOT NULL, 76 O_COMMENT VARCHAR(79) NOT NULL, 77 PRIMARY KEY (O_ORDERKEY),constraint fk_o foreign key(O_CUSTKEY) REFERENCES customer_fk(C_CUSTKEY)); 78 insert into orders_fk values(5999968,12,"F",354575.46,"1992-12-24","3-MEDIUM","Clerk#000000736",0, "cajole blithely ag"); 79 80 CREATE TABLE lineitem_fk( 81 L_ORDERKEY BIGINT NOT NULL, 82 L_PARTKEY INTEGER NOT NULL, 83 L_SUPPKEY INTEGER NOT NULL, 84 L_LINENUMBER INTEGER NOT NULL, 85 L_QUANTITY DECIMAL(15,2) NOT NULL, 86 L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, 87 L_DISCOUNT DECIMAL(15,2) NOT NULL, 88 L_TAX DECIMAL(15,2) NOT NULL, 89 L_RETURNFLAG VARCHAR(1) NOT NULL, 90 L_LINESTATUS VARCHAR(1) NOT NULL, 91 L_SHIPDATE DATE NOT NULL, 92 L_COMMITDATE DATE NOT NULL, 93 L_RECEIPTDATE DATE NOT NULL, 94 L_SHIPINSTRUCT CHAR(25) NOT NULL, 95 L_SHIPMODE CHAR(10) NOT NULL, 96 L_COMMENT VARCHAR(44) NOT NULL, 97 PRIMARY KEY (L_ORDERKEY, L_LINENUMBER),constraint fk_l1 foreign key(L_ORDERKEY) REFERENCES ORDERS_fk(o_orderkey),constraint fk_l2 foreign key(L_PARTKEY) REFERENCES PART_fk(P_PARTKEY),constraint fk_l3 foreign key(L_SUPPKEY) REFERENCES SUPPLIER_fk(S_SUPPKEY)); 98 insert into lineitem_fk values(5999968,199,9991,2,46,63179.16,0.08,0.06,"R","F","1993-09-16","1993-09-21","1993-10-02","COLLECT COD","RAIL","dolites wake"); 99 100 -- update constraint /Master and Slave Tables 101 update nation_fk set n_nationkey=10 where n_nationkey=13; 102 update lineitem_fk set l_partkey=2 where l_suppkey=9991; 103 update PARTSUPP_fk set PS_PARTKEY=40; 104 update orders_fk set O_ORDERKEY=1 where O_CUSTKEY=12; 105 update supplier_fk set s_suppkey=11 where s_nationkey=13; 106 update customer_fk set c_nationkey=6; 107 update part_fk set p_partkey=200 where P_RETAILPRICE=2097.99; 108 update region_fk set r_regionkey=5 where r_name="ASIA"; 109 110 --delete constraint/Master and Slave Tables 111 delete from nation_fk where n_nationkey=13; 112 select * from SUPPLIER_fk; 113 select * from CUSTOMER_fk; 114 delete from lineitem_fk where l_suppkey=9991; 115 select * from partsupp_fk; 116 select * from orders_fk; 117 insert into lineitem_fk values(5999968,199,9991,2,46,63179.16,0.08,0.06,"R","F","1993-09-16","1993-09-21","1993-10-02","COLLECT COD","RAIL","dolites wake"); 118 select * from lineitem_fk; 119 delete from PARTSUPP_fk; 120 select * from part_fk; 121 select * from LINEITEM_fk; 122 insert into PARTSUPP_fk values (199,9991,7872,606.64," according to the final pinto beans: carefully silent requests sleep final"); 123 select * from PARTSUPP_fk; 124 delete from orders_fk where O_CUSTKEY=12; 125 select * from orders_fk ; 126 select * from customer_fk; 127 select * from orders_fk; 128 delete from supplier_fk where s_nationkey=13; 129 select * from partsupp_fk; 130 select * from nation_fk; 131 select * from supplier_fk; 132 delete from customer_fk; 133 select * from nation_fk; 134 select * from orders_fk; 135 delete from part_fk where P_RETAILPRICE=2097.99; 136 select * from PARTSUPP_fk; 137 delete from region_fk where r_name="ASIA"; 138 select * from nation_fk; 139 140 -- insert constraint /Master and Slave Tables 141 insert into region_fk values(3,"ASIA","ges. thinly even pinto beans ca"); 142 select * from region_fk; 143 insert into nation_fk values(1,"VIETNAM",2,"hely enticingly express accounts. even, final"); 144 insert into part_fk values(200,"pink wheat powder burlywood snow","Manufacturer#5","Brand#52","MEDIUM BURNISHED BRASS",49,"LG BOX",2097.99,". special deposits hag"); 145 insert into supplier_fk values(10000,"Supplier#000009991","RnP1Z uvwftshFtf",14,"23-451-948-8464",6785.10,". furiously pending accounts b"); 146 insert into PARTSUPP_fk values (200,10000,7872,606.64," according to the final pinto beans: carefully silent requests sleep final"); 147 insert into customer_fk values(14,"Customer#000149992","iwjVf1MZno1",15,"16-684-999-8810",3417.45,"AUTOMOBILE","luffily final requests integrate slyly. furiously special warhorses are furiously alongside o"); 148 insert into orders_fk values(1,14,"F",354575.46,"1992-12-24","3-MEDIUM","Clerk#000000736",0, "cajole blithely ag"); 149 insert into lineitem_fk values(1,200,10000,2,46,63179.16,0.08,0.06,"R","F","1993-09-16","1993-09-21","1993-10-02","COLLECT COD","RAIL","dolites wake"); 150 151 -- drop constraint /Master and Slave Tables 152 drop table if exists region_fk; 153 drop table if exists nation_fk; 154 drop table if exists part_fk; 155 drop table if exists supplier_fk; 156 drop table if exists partsupp_fk; 157 drop table if exists customer_fk; 158 drop table if exists orders_fk; 159 drop table if exists lineitem_fk; 160 161 drop table if exists lineitem_fk; 162 drop table if exists orders_fk; 163 drop table if exists partsupp_fk; 164 drop table if exists customer_fk; 165 drop table if exists supplier_fk; 166 drop table if exists nation_fk; 167 drop table if exists region_fk; 168 drop table if exists part_fk; 169 170 CREATE TABLE part_fk( 171 P_PARTKEY INTEGER NOT NULL, 172 P_NAME VARCHAR(55) NOT NULL, 173 P_MFGR CHAR(25) NOT NULL, 174 P_BRAND CHAR(10) NOT NULL, 175 P_TYPE VARCHAR(25) NOT NULL, 176 P_SIZE INTEGER NOT NULL, 177 P_CONTAINER CHAR(10) NOT NULL, 178 P_RETAILPRICE DECIMAL(15,2) NOT NULL, 179 P_COMMENT VARCHAR(23) NOT NULL, 180 PRIMARY KEY (P_PARTKEY) 181 ); 182 insert into part_fk values(199,"pink wheat powder burlywood snow","Manufacturer#5","Brand#52","MEDIUM BURNISHED BRASS",49,"LG BOX",2097.99,". special deposits hag"); 183 184 CREATE TABLE region_fk( 185 R_REGIONKEY INTEGER NOT NULL, 186 R_NAME CHAR(25) NOT NULL, 187 R_COMMENT VARCHAR(152), 188 PRIMARY KEY (R_REGIONKEY) 189 ); 190 insert into region_fk values(2,"ASIA","ges. thinly even pinto beans ca"); 191 192 CREATE TABLE NATION_fk( 193 N_NATIONKEY INTEGER NOT NULL, 194 N_NAME CHAR(25) NOT NULL, 195 N_REGIONKEY INTEGER NOT NULL, 196 N_COMMENT VARCHAR(152), 197 PRIMARY KEY (N_NATIONKEY),constraint fk_n foreign key(N_REGIONKEY) REFERENCES region_fk(R_REGIONKEY)on delete CASCADE on update CASCADE 198 ); 199 insert into nation_fk values(13,"VIETNAM",2,"hely enticingly express accounts. even, final"); 200 201 CREATE TABLE supplier_fk( 202 S_SUPPKEY INTEGER NOT NULL, 203 S_NAME CHAR(25) NOT NULL, 204 S_ADDRESS VARCHAR(40) NOT NULL, 205 S_NATIONKEY INTEGER NOT NULL, 206 S_PHONE CHAR(15) NOT NULL, 207 S_ACCTBAL DECIMAL(15,2) NOT NULL, 208 S_COMMENT VARCHAR(101) NOT NULL, 209 PRIMARY KEY (S_SUPPKEY),constraint fk_s foreign key(S_NATIONKEY) REFERENCES nation_fk(N_NATIONKEY)on delete CASCADE on update CASCADE 210 ); 211 insert into supplier_fk values(9991,"Supplier#000009991","RnP1Z uvwftshFtf",13,"23-451-948-8464",6785.10,". furiously pending accounts b"); 212 213 CREATE TABLE PARTSUPP_fk( 214 PS_PARTKEY INTEGER NOT NULL, 215 PS_SUPPKEY INTEGER NOT NULL, 216 PS_AVAILQTY INTEGER NOT NULL, 217 PS_SUPPLYCOST DECIMAL(15,2) NOT NULL, 218 PS_COMMENT VARCHAR(199) NOT NULL, 219 PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY),constraint fk_p1 foreign key(PS_PARTKEY) REFERENCES part_fk(P_PARTKEY)on delete CASCADE on update CASCADE,constraint fk_p2 foreign key(PS_SUPPKEY) REFERENCES supplier_fk(S_SUPPKEY) on delete CASCADE on update CASCADE 220 ); 221 insert into PARTSUPP_fk values (199,9991,7872,606.64," according to the final pinto beans: carefully silent requests sleep final"); 222 223 CREATE TABLE customer_fk( 224 C_CUSTKEY INTEGER NOT NULL, 225 C_NAME VARCHAR(25) NOT NULL, 226 C_ADDRESS VARCHAR(40) NOT NULL, 227 C_NATIONKEY INTEGER NOT NULL, 228 C_PHONE CHAR(15) NOT NULL, 229 C_ACCTBAL DECIMAL(15,2) NOT NULL, 230 C_MKTSEGMENT CHAR(10) NOT NULL, 231 C_COMMENT VARCHAR(117) NOT NULL, 232 PRIMARY KEY (C_CUSTKEY),constraint fk_c foreign key(C_NATIONKEY) REFERENCES nation_fk(N_NATIONKEY) on delete CASCADE on update CASCADE 233 ); 234 insert into customer_fk values(12,"Customer#000149992","iwjVf1MZno1",13,"16-684-999-8810",3417.45,"AUTOMOBILE","luffily final requests integrate slyly. furiously special warhorses are furiously alongside o"); 235 236 CREATE TABLE orders_fk( 237 O_ORDERKEY BIGINT NOT NULL, 238 O_CUSTKEY INTEGER NOT NULL, 239 O_ORDERSTATUS CHAR(1) NOT NULL, 240 O_TOTALPRICE DECIMAL(15,2) NOT NULL, 241 O_ORDERDATE DATE NOT NULL, 242 O_ORDERPRIORITY CHAR(15) NOT NULL, 243 O_CLERK CHAR(15) NOT NULL, 244 O_SHIPPRIORITY INTEGER NOT NULL, 245 O_COMMENT VARCHAR(79) NOT NULL, 246 PRIMARY KEY (O_ORDERKEY),constraint fk_o foreign key(O_CUSTKEY) REFERENCES customer_fk(C_CUSTKEY) on delete CASCADE on update CASCADE); 247 insert into orders_fk values(5999968,12,"F",354575.46,"1992-12-24","3-MEDIUM","Clerk#000000736",0, "cajole blithely ag"); 248 249 CREATE TABLE lineitem_fk( 250 L_ORDERKEY BIGINT NOT NULL, 251 L_PARTKEY INTEGER NOT NULL, 252 L_SUPPKEY INTEGER NOT NULL, 253 L_LINENUMBER INTEGER NOT NULL, 254 L_QUANTITY DECIMAL(15,2) NOT NULL, 255 L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, 256 L_DISCOUNT DECIMAL(15,2) NOT NULL, 257 L_TAX DECIMAL(15,2) NOT NULL, 258 L_RETURNFLAG VARCHAR(1) NOT NULL, 259 L_LINESTATUS VARCHAR(1) NOT NULL, 260 L_SHIPDATE DATE NOT NULL, 261 L_COMMITDATE DATE NOT NULL, 262 L_RECEIPTDATE DATE NOT NULL, 263 L_SHIPINSTRUCT CHAR(25) NOT NULL, 264 L_SHIPMODE CHAR(10) NOT NULL, 265 L_COMMENT VARCHAR(44) NOT NULL, 266 PRIMARY KEY (L_ORDERKEY, L_LINENUMBER),constraint fk_l1 foreign key(L_ORDERKEY) REFERENCES ORDERS_fk(o_orderkey)on delete CASCADE on update CASCADE,constraint fk_l2 foreign key(L_PARTKEY,L_SUPPKEY) REFERENCES PARTSUPP_fk(PS_PARTKEY,PS_SUPPKEY) on delete CASCADE on update CASCADE); 267 insert into lineitem_fk values(5999968,199,9991,2,46,63179.16,0.08,0.06,"R","F","1993-09-16","1993-09-21","1993-10-02","COLLECT COD","RAIL","dolites wake"); 268 269 -- update constraint 270 update nation_fk set n_nationkey=10 where n_nationkey=13; 271 select * from supplier_fk; 272 select * from customer_fk; 273 update lineitem_fk set l_partkey=2 where l_suppkey=9991; 274 select * from PARTSUPP_fk; 275 update PARTSUPP_fk set PS_PARTKEY=40; 276 select * from lineitem_fk; 277 update orders_fk set O_ORDERKEY=1 where O_CUSTKEY=12; 278 select * from lineitem_fk; 279 update supplier_fk set s_suppkey=11 where s_nationkey=10; 280 select * from supplier_fk; 281 select PS_SUPPKEY from PARTSUPP_fk ; 282 update customer_fk set c_nationkey=20; 283 update part_fk set p_partkey=200 where P_RETAILPRICE=2097.99; 284 select PS_PARTKEY from PARTSUPP_fk; 285 update region_fk set r_regionkey=5 where r_name="ASIA"; 286 select N_REGIONKEY from NATION_fk; 287 288 --delete constraint 289 select * from nation_fk; 290 delete from nation_fk where n_nationkey=10; 291 select * from SUPPLIER_fk; 292 select * from CUSTOMER_fk; 293 select * from lineitem_fk; 294 delete from lineitem_fk where l_suppkey=9991; 295 select * from partsupp_fk; 296 select * from orders_fk; 297 insert into lineitem_fk values(5999968,199,9991,2,46,63179.16,0.08,0.06,"R","F","1993-09-16","1993-09-21","1993-10-02","COLLECT COD","RAIL","dolites wake"); 298 delete from PARTSUPP_fk; 299 select * from part_fk; 300 select * from LINEITEM_fk; 301 insert into PARTSUPP_fk values (199,9991,7872,606.64," according to the final pinto beans: carefully silent requests sleep final"); 302 delete from orders_fk where O_CUSTKEY=12; 303 select * from customer_fk; 304 select * from lineitem_fk; 305 insert into orders_fk values(5999968,12,"F",354575.46,"1992-12-24","3-MEDIUM","Clerk#000000736",0, "cajole blithely ag"); 306 select * from orders_fk; 307 delete from supplier_fk where s_nationkey=10; 308 select * from partsupp_fk; 309 select * from nation_fk; 310 select * from supplier_fk; 311 insert into supplier_fk values(10000,"Supplier#000009991","RnP1Z uvwftshFtf",13,"23-451-948-8464",6785.10,". furiously pending accounts b"); 312 delete from customer_fk; 313 select * from nation_fk; 314 select * from orders_fk; 315 insert into customer_fk values(12,"Customer#000149992","iwjVf1MZno1",13,"16-684-999-8810",3417.45,"AUTOMOBILE","luffily final requests integrate slyly. furiously special warhorses are furiously alongside o"); 316 delete from part_fk where P_RETAILPRICE=2097.99; 317 select * from PARTSUPP_fk; 318 insert into part_fk values(199,"pink wheat powder burlywood snow","Manufacturer#5","Brand#52","MEDIUM BURNISHED BRASS",49,"LG BOX",2097.99,". special deposits hag"); 319 delete from region_fk where r_name="ASIA"; 320 select * from nation_fk; 321 insert into region_fk values(2,"ASIA","ges. thinly even pinto beans ca"); 322 insert into region_fk values(3,"ASIA","ges. thinly even pinto beans ca"); 323 select * from region_fk; 324 insert into nation_fk values(1,"VIETNAM",2,"hely enticingly express accounts. even, final"); 325 insert into part_fk values(200,"pink wheat powder burlywood snow","Manufacturer#5","Brand#52","MEDIUM BURNISHED BRASS",49,"LG BOX",2097.99,". special deposits hag"); 326 327 insert into supplier_fk values(10000,"Supplier#000009991","RnP1Z uvwftshFtf",14,"23-451-948-8464",6785.10,". furiously pending accounts b"); 328 insert into PARTSUPP_fk values (200,10000,7872,606.64," according to the final pinto beans: carefully silent requests sleep final"); 329 insert into customer_fk values(14,"Customer#000149992","iwjVf1MZno1",15,"16-684-999-8810",3417.45,"AUTOMOBILE","luffily final requests integrate slyly. furiously special warhorses are furiously alongside o"); 330 insert into orders_fk values(1,14,"F",354575.46,"1992-12-24","3-MEDIUM","Clerk#000000736",0, "cajole blithely ag"); 331 insert into lineitem_fk values(1,200,10000,2,46,63179.16,0.08,0.06,"R","F","1993-09-16","1993-09-21","1993-10-02","COLLECT COD","RAIL","dolites wake"); 332 drop table if exists lineitem_fk; 333 drop table if exists partsupp_fk; 334 drop table if exists orders_fk; 335 drop table if exists part_fk; 336 drop table if exists supplier_fk; 337 drop table if exists customer_fk; 338 drop table if exists nation_fk; 339 drop table if exists region_fk;