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