github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/alter_table_check_foreign_key.result (about) 1 drop database if exists db8; 2 create database db8; 3 use db8; 4 drop table if exists dept; 5 create table dept( 6 deptno int unsigned auto_increment COMMENT '部门编号', 7 dname varchar(15) COMMENT '部门名称', 8 loc varchar(50) COMMENT '部门所在位置', 9 primary key(deptno) 10 ) COMMENT='部门表'; 11 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 12 INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 13 INSERT INTO dept VALUES (30,'SALES','CHICAGO'); 14 INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON'); 15 drop table if exists emp; 16 create table emp( 17 empno int unsigned auto_increment COMMENT '雇员编号', 18 ename varchar(15) COMMENT '雇员姓名', 19 job varchar(10) COMMENT '雇员职位', 20 mgr int unsigned COMMENT '雇员对应的领导的编号', 21 hiredate date COMMENT '雇员的雇佣日期', 22 sal decimal(7,2) COMMENT '雇员的基本工资', 23 comm decimal(7,2) COMMENT '奖金', 24 deptno int unsigned COMMENT '所在部门', 25 primary key(empno), 26 constraint `c1` FOREIGN KEY (deptno) REFERENCES dept(deptno) 27 ); 28 INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 29 INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 30 INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 31 INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 32 INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 33 INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 34 INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 35 INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 36 INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 37 INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 38 INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 39 INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 40 INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 41 INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 42 alter table emp modify deptno varchar(20); 43 Cannot change column 'deptno': used in a foreign key constraint 'c1' 44 alter table emp modify deptno bigint; 45 Cannot change column 'deptno': used in a foreign key constraint 'c1' 46 alter table emp modify deptno int unsigned; 47 desc emp; 48 Field Type Null Key Default Extra Comment 49 empno INT UNSIGNED(32) NO PRI null 雇员编号 50 ename VARCHAR(15) YES null 雇员姓名 51 job VARCHAR(10) YES null 雇员职位 52 mgr INT UNSIGNED(32) YES null 雇员对应的领导的编号 53 hiredate DATE(0) YES null 雇员的雇佣日期 54 sal DECIMAL64(7) YES null 雇员的基本工资 55 comm DECIMAL64(7) YES null 奖金 56 deptno INT UNSIGNED(32) YES MUL null 57 alter table emp modify deptno int unsigned default 100; 58 desc emp; 59 Field Type Null Key Default Extra Comment 60 empno INT UNSIGNED(32) NO PRI null 雇员编号 61 ename VARCHAR(15) YES null 雇员姓名 62 job VARCHAR(10) YES null 雇员职位 63 mgr INT UNSIGNED(32) YES null 雇员对应的领导的编号 64 hiredate DATE(0) YES null 雇员的雇佣日期 65 sal DECIMAL64(7) YES null 雇员的基本工资 66 comm DECIMAL64(7) YES null 奖金 67 deptno INT UNSIGNED(32) YES MUL 100 68 alter table emp change deptno deptid varchar(20); 69 Cannot change column 'deptno': used in a foreign key constraint 'c1' 70 alter table emp change deptno deptid bigint; 71 Cannot change column 'deptno': used in a foreign key constraint 'c1' 72 alter table emp change deptno deptid int unsigned; 73 desc emp; 74 Field Type Null Key Default Extra Comment 75 empno INT UNSIGNED(32) NO PRI null 雇员编号 76 ename VARCHAR(15) YES null 雇员姓名 77 job VARCHAR(10) YES null 雇员职位 78 mgr INT UNSIGNED(32) YES null 雇员对应的领导的编号 79 hiredate DATE(0) YES null 雇员的雇佣日期 80 sal DECIMAL64(7) YES null 雇员的基本工资 81 comm DECIMAL64(7) YES null 奖金 82 deptid INT UNSIGNED(32) YES MUL null 83 INSERT INTO emp VALUES (7990,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,100); 84 internal error: Cannot add or update a child row: a foreign key constraint fails 85 INSERT INTO emp VALUES (7990,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 86 select * from emp; 87 empno ename job mgr hiredate sal comm deptid 88 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 89 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 90 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 91 7566 JONES MANAGER 7839 1981-04-02 2975.00 null 20 92 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 93 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 null 30 94 7782 CLARK MANAGER 7839 1981-06-09 2450.00 null 10 95 7788 SCOTT ANALYST 7566 0087-07-13 3000.00 null 20 96 7839 KING PRESIDENT null 1981-11-17 5000.00 null 10 97 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 98 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 99 7900 JAMES CLERK 7698 1981-12-03 950.00 null 30 100 7902 FORD ANALYST 7566 1981-12-03 3000.00 null 20 101 7934 MILLER CLERK 7782 1982-01-23 1300.00 null 10 102 7990 MILLER CLERK 7782 1982-01-23 1300.00 null 10 103 drop table emp; 104 drop table dept; 105 drop table if exists dept; 106 create table dept( 107 deptno int unsigned auto_increment COMMENT '部门编号', 108 dname varchar(15) COMMENT '部门名称', 109 loc varchar(50) COMMENT '部门所在位置', 110 primary key(deptno) 111 ) COMMENT='部门表'; 112 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 113 INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 114 INSERT INTO dept VALUES (30,'SALES','CHICAGO'); 115 INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON'); 116 drop table if exists emp; 117 create table emp( 118 empno int unsigned auto_increment COMMENT '雇员编号', 119 ename varchar(15) COMMENT '雇员姓名', 120 job varchar(10) COMMENT '雇员职位', 121 mgr int unsigned COMMENT '雇员对应的领导的编号', 122 hiredate date COMMENT '雇员的雇佣日期', 123 sal decimal(7,2) COMMENT '雇员的基本工资', 124 comm decimal(7,2) COMMENT '奖金', 125 deptno int unsigned COMMENT '所在部门', 126 primary key(empno), 127 FOREIGN KEY (deptno) REFERENCES dept(deptno) 128 ); 129 INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 130 INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 131 INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 132 INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 133 INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 134 INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 135 INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 136 INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 137 INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 138 INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 139 INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 140 INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 141 INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 142 INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 143 alter table emp ALTER COLUMN deptno SET DEFAULT 10; 144 desc emp; 145 Field Type Null Key Default Extra Comment 146 empno INT UNSIGNED(32) NO PRI null 雇员编号 147 ename VARCHAR(15) YES null 雇员姓名 148 job VARCHAR(10) YES null 雇员职位 149 mgr INT UNSIGNED(32) YES null 雇员对应的领导的编号 150 hiredate DATE(0) YES null 雇员的雇佣日期 151 sal DECIMAL64(7) YES null 雇员的基本工资 152 comm DECIMAL64(7) YES null 奖金 153 deptno INT UNSIGNED(32) YES MUL 10 所在部门 154 alter table emp ALTER COLUMN deptno SET INVISIBLE; 155 desc emp; 156 Field Type Null Key Default Extra Comment 157 empno INT UNSIGNED(32) NO PRI null 雇员编号 158 ename VARCHAR(15) YES null 雇员姓名 159 job VARCHAR(10) YES null 雇员职位 160 mgr INT UNSIGNED(32) YES null 雇员对应的领导的编号 161 hiredate DATE(0) YES null 雇员的雇佣日期 162 sal DECIMAL64(7) YES null 雇员的基本工资 163 comm DECIMAL64(7) YES null 奖金 164 deptno INT UNSIGNED(32) YES MUL 10 所在部门 165 alter table emp ALTER COLUMN deptno drop default; 166 desc emp; 167 Field Type Null Key Default Extra Comment 168 empno INT UNSIGNED(32) NO PRI null 雇员编号 169 ename VARCHAR(15) YES null 雇员姓名 170 job VARCHAR(10) YES null 雇员职位 171 mgr INT UNSIGNED(32) YES null 雇员对应的领导的编号 172 hiredate DATE(0) YES null 雇员的雇佣日期 173 sal DECIMAL64(7) YES null 雇员的基本工资 174 comm DECIMAL64(7) YES null 奖金 175 deptno INT UNSIGNED(32) YES MUL null 所在部门 176 select * from emp; 177 empno ename job mgr hiredate sal comm deptno 178 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 179 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 180 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 181 7566 JONES MANAGER 7839 1981-04-02 2975.00 null 20 182 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 183 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 null 30 184 7782 CLARK MANAGER 7839 1981-06-09 2450.00 null 10 185 7788 SCOTT ANALYST 7566 0087-07-13 3000.00 null 20 186 7839 KING PRESIDENT null 1981-11-17 5000.00 null 10 187 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 188 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 189 7900 JAMES CLERK 7698 1981-12-03 950.00 null 30 190 7902 FORD ANALYST 7566 1981-12-03 3000.00 null 20 191 7934 MILLER CLERK 7782 1982-01-23 1300.00 null 10 192 alter table emp rename column deptno to deptid; 193 desc emp; 194 Field Type Null Key Default Extra Comment 195 empno INT UNSIGNED(32) NO PRI null 雇员编号 196 ename VARCHAR(15) YES null 雇员姓名 197 job VARCHAR(10) YES null 雇员职位 198 mgr INT UNSIGNED(32) YES null 雇员对应的领导的编号 199 hiredate DATE(0) YES null 雇员的雇佣日期 200 sal DECIMAL64(7) YES null 雇员的基本工资 201 comm DECIMAL64(7) YES null 奖金 202 deptid INT UNSIGNED(32) YES MUL null 所在部门 203 select * from emp; 204 empno ename job mgr hiredate sal comm deptid 205 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 206 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 207 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 208 7566 JONES MANAGER 7839 1981-04-02 2975.00 null 20 209 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 210 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 null 30 211 7782 CLARK MANAGER 7839 1981-06-09 2450.00 null 10 212 7788 SCOTT ANALYST 7566 0087-07-13 3000.00 null 20 213 7839 KING PRESIDENT null 1981-11-17 5000.00 null 10 214 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 215 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 216 7900 JAMES CLERK 7698 1981-12-03 950.00 null 30 217 7902 FORD ANALYST 7566 1981-12-03 3000.00 null 20 218 7934 MILLER CLERK 7782 1982-01-23 1300.00 null 10 219 INSERT INTO emp VALUES (7990,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,100); 220 internal error: Cannot add or update a child row: a foreign key constraint fails 221 INSERT INTO emp VALUES (7990,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 222 select * from emp; 223 empno ename job mgr hiredate sal comm deptid 224 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 225 7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 226 7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 227 7566 JONES MANAGER 7839 1981-04-02 2975.00 null 20 228 7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 229 7698 BLAKE MANAGER 7839 1981-05-01 2850.00 null 30 230 7782 CLARK MANAGER 7839 1981-06-09 2450.00 null 10 231 7788 SCOTT ANALYST 7566 0087-07-13 3000.00 null 20 232 7839 KING PRESIDENT null 1981-11-17 5000.00 null 10 233 7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 234 7876 ADAMS CLERK 7788 0087-07-13 1100.00 null 20 235 7900 JAMES CLERK 7698 1981-12-03 950.00 null 30 236 7902 FORD ANALYST 7566 1981-12-03 3000.00 null 20 237 7934 MILLER CLERK 7782 1982-01-23 1300.00 null 10 238 7990 MILLER CLERK 7782 1982-01-23 1300.00 null 10 239 drop table emp; 240 drop table dept; 241 CREATE TABLE product ( 242 category INT NOT NULL, 243 id INT NOT NULL, 244 name VARCHAR(20) NOT NULL, 245 price DECIMAL, 246 PRIMARY KEY (category, id) 247 ); 248 INSERT INTO product VALUES (10, 1, '小米手机', 2799.00); 249 INSERT INTO product VALUES (10, 2, '苹果手机', 6499.00); 250 INSERT INTO product VALUES (20, 3, 'Surface笔记本', 15388.00); 251 INSERT INTO product VALUES (30, 4, '特斯拉电动车',250000.00); 252 CREATE TABLE customer ( 253 id INT NOT NULL, 254 name varchar(50) NOT NULL, 255 PRIMARY KEY (id) 256 ); 257 INSERT INTO customer VALUES (1, '小米'); 258 INSERT INTO customer VALUES (2, '苹果'); 259 INSERT INTO customer VALUES (3, '微软'); 260 INSERT INTO customer VALUES (4, '特斯拉'); 261 CREATE TABLE product_order ( 262 no INT NOT NULL AUTO_INCREMENT, 263 product_category INT NOT NULL, 264 product_id INT NOT NULL, 265 customer_id INT NOT NULL, 266 order_date datetime NOT NULL, 267 PRIMARY KEY (no), 268 INDEX(product_category, product_id), 269 INDEX(customer_id), 270 constraint `c1` FOREIGN KEY (product_category, product_id) REFERENCES product (category, id) ON DELETE RESTRICT ON UPDATE CASCADE, 271 constraint `c2` FOREIGN KEY (customer_id) REFERENCES customer (id) 272 ); 273 INSERT INTO product_order VALUES (1, 10, 1, 1, '2016-12-02 15:41:39'); 274 INSERT INTO product_order VALUES (2, 10, 2, 2, '2016-12-01 15:42:42'); 275 INSERT INTO product_order VALUES (3, 20, 3, 3, '2016-12-06 15:43:26'); 276 INSERT INTO product_order VALUES (4, 30, 4, 3, '2016-12-31 15:43:26'); 277 desc product_order; 278 Field Type Null Key Default Extra Comment 279 no INT(32) NO PRI null 280 product_category INT(32) NO MUL null 281 product_id INT(32) NO MUL null 282 customer_id INT(32) NO MUL null 283 order_date DATETIME(0) NO null 284 select * from product_order; 285 no product_category product_id customer_id order_date 286 1 10 1 1 2016-12-02 15:41:39 287 2 10 2 2 2016-12-01 15:42:42 288 3 20 3 3 2016-12-06 15:43:26 289 4 30 4 3 2016-12-31 15:43:26 290 alter table product_order change product_category product_kind varchar(20); 291 Cannot change column 'product_category': used in a foreign key constraint 'c1' 292 alter table product_order change product_category product_kind bigint; 293 Cannot change column 'product_category': used in a foreign key constraint 'c1' 294 alter table product_order modify product_id varchar(20); 295 Cannot change column 'product_id': used in a foreign key constraint 'c1' 296 alter table product_order modify product_id bigint; 297 Cannot change column 'product_id': used in a foreign key constraint 'c1' 298 alter table product_order change product_category product_kind int unsigned; 299 Cannot change column 'product_category': used in a foreign key constraint 'c1' 300 alter table product_order change product_category product_kind int; 301 desc product_order; 302 Field Type Null Key Default Extra Comment 303 no INT(32) NO PRI null 304 product_kind INT(32) YES MUL null 305 product_id INT(32) NO MUL null 306 customer_id INT(32) NO MUL null 307 order_date DATETIME(0) NO null 308 select * from product_order; 309 no product_kind product_id customer_id order_date 310 1 10 1 1 2016-12-02 15:41:39 311 2 10 2 2 2016-12-01 15:42:42 312 3 20 3 3 2016-12-06 15:43:26 313 4 30 4 3 2016-12-31 15:43:26 314 INSERT INTO product_order VALUES (4, 30, 4, 3, '2016-12-31 15:43:26'); 315 Duplicate entry '([^']*)' for key '([^']*)' 316 INSERT INTO product_order VALUES (5, 30, 5, 3, '2016-12-31 15:43:26'); 317 internal error: Cannot add or update a child row: a foreign key constraint fails 318 INSERT INTO product_order VALUES (5, 30, 4, 4, '2016-12-31 15:43:26'); 319 select *from product_order; 320 no product_kind product_id customer_id order_date 321 1 10 1 1 2016-12-02 15:41:39 322 2 10 2 2 2016-12-01 15:42:42 323 3 20 3 3 2016-12-06 15:43:26 324 4 30 4 3 2016-12-31 15:43:26 325 5 30 4 4 2016-12-31 15:43:26 326 INSERT INTO product_order VALUES (6, 30, 4, 5, '2016-12-31 15:43:26'); 327 internal error: Cannot add or update a child row: a foreign key constraint fails 328 drop table product_order; 329 drop table customer; 330 drop table product; 331 drop table if exists organization; 332 create table organization ( 333 id int auto_increment primary key, 334 name varchar(255) not null , 335 parent_id int default null, 336 foreign key (parent_id) references organization(id) 337 ); 338 insert into organization (id, name, parent_id ) values (1, 'ceo', null); 339 insert into organization (id, name, parent_id ) values (2, 'cto', null); 340 insert into organization (id, name, parent_id ) values (3, 'dev', 2); 341 insert into organization (id, name, parent_id ) values (4, 'test', 2); 342 insert into organization (id, name, parent_id ) values (5, 'marketing', 1); 343 insert into organization (id, name, parent_id ) values (6, 'finance', 1); 344 select * from organization; 345 id name parent_id 346 1 ceo null 347 2 cto null 348 3 dev 2 349 4 test 2 350 5 marketing 1 351 6 finance 1 352 insert into organization (id, name, parent_id ) values (7, 'unknown', 99); 353 Cannot add or update a child row: a foreign key constraint fails 354 insert into organization (id, name ) values (8, 'unknown'); 355 select * from organization; 356 id name parent_id 357 1 ceo null 358 2 cto null 359 3 dev 2 360 4 test 2 361 5 marketing 1 362 6 finance 1 363 8 unknown null 364 drop table organization; 365 drop database if exists db8;