github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/alter_table_check_foreignkey_reference.sql (about) 1 drop database if exists db7; 2 create database db7; 3 use db7; 4 ------------------------------------------------------------------------------------------------------------------------ 5 drop table if exists dept; 6 create table dept( 7 deptno int unsigned auto_increment COMMENT '部门编号', 8 dname varchar(15) COMMENT '部门名称', 9 loc varchar(50) COMMENT '部门所在位置', 10 primary key(deptno) 11 ) COMMENT='部门表'; 12 13 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 14 INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 15 INSERT INTO dept VALUES (30,'SALES','CHICAGO'); 16 INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON'); 17 18 drop table if exists emp; 19 create table emp( 20 empno int unsigned auto_increment COMMENT '雇员编号', 21 ename varchar(15) COMMENT '雇员姓名', 22 job varchar(10) COMMENT '雇员职位', 23 mgr int unsigned COMMENT '雇员对应的领导的编号', 24 hiredate date COMMENT '雇员的雇佣日期', 25 sal decimal(7,2) COMMENT '雇员的基本工资', 26 comm decimal(7,2) COMMENT '奖金', 27 deptno int unsigned COMMENT '所在部门', 28 primary key(empno), 29 constraint `c1` FOREIGN KEY (deptno) REFERENCES dept(deptno) 30 ); 31 32 INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 33 INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 34 INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 35 INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 36 INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 37 INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 38 INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 39 INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 40 INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 41 INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 42 INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 43 INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 44 INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 45 INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 46 47 --1.如果修改的列被其他表作为外键依赖,则不能使用change， modify进行修改 48 --------------------------------------------------------------------------------------------------------------------- 49 alter table dept modify deptno int unsigned after dname; 50 --ERROR 1833 (HY000): Cannot change column 'deptno': used in a foreign key constraint 'emp_ibfk_1' of table 'db1.emp' 51 52 alter table dept modify deptno int unsigned; 53 --ERROR 1833 (HY000): Cannot change column 'deptno': used in a foreign key constraint 'emp_ibfk_1' of table 'db1.emp' 54 55 alter table dept change deptno deptId int unsigned after dname; 56 --ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE. 57 58 alter table dept change deptno deptId int unsigned; 59 --ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE. 60 61 alter table dept change deptno deptno int unsigned; 62 --ERROR 1833 (HY000): Cannot change column 'deptno': used in a foreign key constraint 'emp_ibfk_1' of table 'db1.emp' 63 64 alter table dept change deptno deptno int unsigned after dname; 65 --ERROR 1833 (HY000): Cannot change column 'deptno': used in a foreign key constraint 'emp_ibfk_1' of table 'db1.emp' 66 --------------------------------------------------------------------------------------------------------------------- 67 alter table dept modify deptno int unsigned auto_increment; 68 --success 69 desc dept; 70 select * from dept; 71 72 alter table dept modify deptno int unsigned auto_increment after dname; 73 --success 74 desc dept; 75 select * from dept; 76 77 INSERT INTO emp VALUES (7990,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,100); 78 --ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)) 79 80 INSERT INTO emp VALUES (7990,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 81 select * from emp; 82 83 drop table emp; 84 drop table dept; 85 ------------------------------------------------------------------------------------------------------------------------ 86 drop table if exists dept; 87 create table dept( 88 deptno int unsigned COMMENT '部门编号', 89 dname varchar(15) COMMENT '部门名称', 90 loc varchar(50) COMMENT '部门所在位置', 91 primary key(deptno) 92 ) COMMENT='部门表'; 93 94 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 95 INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 96 INSERT INTO dept VALUES (30,'SALES','CHICAGO'); 97 INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON'); 98 99 drop table if exists emp; 100 create table emp( 101 empno int unsigned auto_increment COMMENT '雇员编号', 102 ename varchar(15) COMMENT '雇员姓名', 103 job varchar(10) COMMENT '雇员职位', 104 mgr int unsigned COMMENT '雇员对应的领导的编号', 105 hiredate date COMMENT '雇员的雇佣日期', 106 sal decimal(7,2) COMMENT '雇员的基本工资', 107 comm decimal(7,2) COMMENT '奖金', 108 deptno int unsigned COMMENT '所在部门', 109 primary key(empno), 110 constraint `c1` FOREIGN KEY (deptno) REFERENCES dept(deptno) 111 ); 112 113 INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 114 INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 115 INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 116 INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 117 INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 118 INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 119 INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 120 INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 121 INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 122 INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 123 INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 124 INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 125 INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 126 INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 127 128 alter table dept modify deptno int unsigned auto_increment; 129 --ERROR 1833 (HY000): Cannot change column 'deptno': used in a foreign key constraint 'emp_ibfk_1' of table 'db1.emp' 130 131 alter table dept modify deptno int unsigned auto_increment after dname; 132 --ERROR 1833 (HY000): Cannot change column 'deptno': used in a foreign key constraint 'emp_ibfk_1' of table 'db1.emp' 133 134 alter table dept change deptno deptId int unsigned auto_increment after dname; 135 --ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE. 136 137 alter table dept change deptno deptId int unsigned auto_increment; 138 --ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE. 139 140 alter table dept change deptno deptId int unsigned after dname; 141 --success 142 desc dept; 143 144 INSERT INTO emp VALUES (7990,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,100); 145 --ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)) 146 INSERT INTO emp VALUES (7990,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 147 148 select * from emp; 149 150 drop table emp; 151 drop table dept; 152 ------------------------------------------------------------------------------------------------------------------------ 153 drop table if exists dept; 154 create table dept( 155 deptno int unsigned COMMENT '部门编号', 156 dname varchar(15) COMMENT '部门名称', 157 loc varchar(50) COMMENT '部门所在位置', 158 primary key(deptno) 159 ) COMMENT='部门表'; 160 161 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 162 INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 163 INSERT INTO dept VALUES (30,'SALES','CHICAGO'); 164 INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON'); 165 166 drop table if exists emp; 167 create table emp( 168 empno int unsigned auto_increment COMMENT '雇员编号', 169 ename varchar(15) COMMENT '雇员姓名', 170 job varchar(10) COMMENT '雇员职位', 171 mgr int unsigned COMMENT '雇员对应的领导的编号', 172 hiredate date COMMENT '雇员的雇佣日期', 173 sal decimal(7,2) COMMENT '雇员的基本工资', 174 comm decimal(7,2) COMMENT '奖金', 175 deptno int unsigned COMMENT '所在部门', 176 primary key(empno), 177 constraint `c1` FOREIGN KEY (deptno) REFERENCES dept(deptno) 178 ); 179 180 INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 181 INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 182 INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 183 INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 184 INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 185 INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 186 INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 187 INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 188 INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 189 INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 190 INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 191 INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 192 INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 193 INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 194 195 alter table dept modify deptno int unsigned default 10; 196 desc dept; 197 select * from dept; 198 --success 199 200 alter table dept modify deptno int unsigned after dname; 201 desc dept; 202 select * from dept; 203 204 --success 205 INSERT INTO emp VALUES (7990,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,100); 206 --ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)) 207 INSERT INTO emp VALUES (7990,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 208 209 select * from emp; 210 211 drop table emp; 212 drop table dept; 213 ------------------------------------------------------------------------------------------------------------------------ 214 drop table if exists dept; 215 create table dept( 216 deptno int unsigned auto_increment COMMENT '部门编号', 217 dname varchar(15) COMMENT '部门名称', 218 loc varchar(50) COMMENT '部门所在位置', 219 primary key(deptno) 220 ) COMMENT='部门表'; 221 222 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 223 INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 224 INSERT INTO dept VALUES (30,'SALES','CHICAGO'); 225 INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON'); 226 227 drop table if exists emp; 228 create table emp( 229 empno int unsigned auto_increment COMMENT '雇员编号', 230 ename varchar(15) COMMENT '雇员姓名', 231 job varchar(10) COMMENT '雇员职位', 232 mgr int unsigned COMMENT '雇员对应的领导的编号', 233 hiredate date COMMENT '雇员的雇佣日期', 234 sal decimal(7,2) COMMENT '雇员的基本工资', 235 comm decimal(7,2) COMMENT '奖金', 236 deptno int unsigned COMMENT '所在部门', 237 primary key(empno), 238 constraint `c1` FOREIGN KEY (deptno) REFERENCES dept(deptno) 239 ); 240 241 INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 242 INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); 243 INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 244 INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 245 INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 246 INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 247 INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 248 INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'0087-07-13',3000,NULL,20); 249 INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); 250 INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 251 INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'0087-07-13',1100,NULL,20); 252 INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 253 INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 254 INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 255 256 257 alter table dept ALTER COLUMN deptno SET DEFAULT 10; 258 --success 259 desc dept; 260 261 alter table dept ALTER COLUMN deptno SET INVISIBLE; 262 --success 263 desc dept; 264 265 alter table dept ALTER COLUMN deptno drop default; 266 --success 267 desc dept; 268 269 alter table dept rename column deptno to deptid; 270 --success 271 desc dept; 272 select * from dept; 273 274 --success 275 INSERT INTO emp VALUES (7990,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,100); 276 --ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)) 277 INSERT INTO emp VALUES (7990,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 278 select * from emp; 279 280 drop table emp; 281 drop table dept; 282 283 drop database if exists db7;