github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/alter_table_add_drop_primary_key.result (about) 1 drop table if exists pri01; 2 create table pri01 (col1 int, col2 decimal); 3 insert into pri01 (col1, col2) values (1,2378.328839842); 4 insert into pri01 values (234, -3923.2342342); 5 select * from pri01; 6 col1 col2 7 1 2378 8 234 -3923 9 show create table pri01; 10 Table Create Table 11 pri01 CREATE TABLE `pri01` (\n`col1` INT DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 12 alter table pri01 add constraint primary key(col1); 13 insert into pri01 values (23423, 32432543.3242); 14 insert into pri01 values (234, -3923.2342342); 15 Duplicate entry '234' for key 'col1' 16 show columns from pri01; 17 Field Type Null Key Default Extra Comment 18 col1 INT(32) NO PRI null 19 col2 DECIMAL128(38) YES null 20 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'pri01' and COLUMN_NAME not like '__mo%'; 21 table_name column_name data_type is_nullable 22 pri01 col1 INT NO 23 pri01 col2 DECIMAL128 YES 24 drop table pri01; 25 drop table if exists pri02; 26 create table pri02 (col1 char, col2 bigint unsigned); 27 insert into pri02 (col1, col2) values ('a', 327349284903284032); 28 insert into pri02 values ('*', 3289323423); 29 insert into pri02 values ('*', 328932342342424); 30 select * from pri02; 31 col1 col2 32 a 327349284903284032 33 * 3289323423 34 * 328932342342424 35 alter table pri02 add constraint primary key (col1); 36 Duplicate entry '*' for key 'col1' 37 show create table pri02; 38 Table Create Table 39 pri02 CREATE TABLE `pri02` (\n`col1` CHAR(1) DEFAULT NULL,\n`col2` BIGINT UNSIGNED DEFAULT NULL\n) 40 show columns from pri02; 41 Field Type Null Key Default Extra Comment 42 col1 CHAR(1) YES null 43 col2 BIGINT UNSIGNED(64) YES null 44 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'pri02' and COLUMN_NAME not like '__mo%'; 45 table_name column_name data_type is_nullable 46 pri02 col1 CHAR YES 47 pri02 col2 BIGINT UNSIGNED YES 48 drop table pri02; 49 drop table if exists pri03; 50 create table pri03 (col1 char, col2 bigint unsigned); 51 insert into pri03 (col1, col2) values ('a', 327349284903284032); 52 insert into pri03 values ('*', 3289323423); 53 select * from pri03; 54 col1 col2 55 a 327349284903284032 56 * 3289323423 57 alter table pri03 add constraint primary key (col1); 58 show create table pri03; 59 Table Create Table 60 pri03 CREATE TABLE `pri03` (\n`col1` CHAR(1) NOT NULL,\n`col2` BIGINT UNSIGNED DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 61 show columns from pri03; 62 Field Type Null Key Default Extra Comment 63 col1 CHAR(1) NO PRI null 64 col2 BIGINT UNSIGNED(64) YES null 65 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'pri03' and COLUMN_NAME not like '__mo%'; 66 table_name column_name data_type is_nullable 67 pri03 col1 CHAR NO 68 pri03 col2 BIGINT UNSIGNED YES 69 drop table pri03; 70 drop table if exists pri04; 71 create table pri04 (col1 varchar(100), col2 float); 72 insert into pri04 (col1, col2) values ('databaseDATABASE 数据库数据库系统', -32734928490.3284032); 73 insert into pri04 values ('3782973804u2databasejnwfhui34数据库endfcioc', 3289323423); 74 insert into pri04 values (null, 378270389824324); 75 select * from pri04; 76 col1 col2 77 databaseDATABASE 数据库数据库系统 -3.2734929E10 78 3782973804u2databasejnwfhui34数据库endfcioc 3.2893235E9 79 null 3.782704E14 80 alter table pri04 add constraint primary key (col1); 81 constraint violation: Column 'col1' cannot be null 82 show create table pri04; 83 Table Create Table 84 pri04 CREATE TABLE `pri04` (\n`col1` VARCHAR(100) DEFAULT NULL,\n`col2` FLOAT DEFAULT NULL\n) 85 show columns from pri04; 86 Field Type Null Key Default Extra Comment 87 col1 VARCHAR(100) YES null 88 col2 FLOAT(0) YES null 89 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'pri04' and COLUMN_NAME not like '__mo%'; 90 table_name column_name data_type is_nullable 91 pri04 col1 VARCHAR YES 92 pri04 col2 FLOAT YES 93 drop table pri04; 94 drop table if exists pri05; 95 create table pri05 (col1 date, col2 double); 96 insert into pri05 (col1, col2) values ('1997-01-13', -32734928490.3284032); 97 insert into pri05 values ('2023-08-18', 3289323423); 98 select * from pri05; 99 col1 col2 100 1997-01-13 -3.2734928490328403E10 101 2023-08-18 3.289323423E9 102 alter table pri05 add constraint primary key (col1); 103 show create table pri05; 104 Table Create Table 105 pri05 CREATE TABLE `pri05` (\n`col1` DATE NOT NULL,\n`col2` DOUBLE DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 106 show columns from pri05; 107 Field Type Null Key Default Extra Comment 108 col1 DATE(0) NO PRI null 109 col2 DOUBLE(0) YES null 110 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'pri05' and COLUMN_NAME not like '__mo%'; 111 table_name column_name data_type is_nullable 112 pri05 col1 DATE NO 113 pri05 col2 DOUBLE YES 114 drop table pri05; 115 drop table if exists pri06; 116 create table pri06 (col1 smallint default null, col2 double); 117 insert into pri06 (col1, col2) values (100, -32734928490.3284032); 118 insert into pri06 values (200, 3289323423); 119 select * from pri06; 120 col1 col2 121 100 -3.2734928490328403E10 122 200 3.289323423E9 123 alter table pri06 add constraint primary key (col1); 124 show create table pri06; 125 Table Create Table 126 pri06 CREATE TABLE `pri06` (\n`col1` SMALLINT NOT NULL,\n`col2` DOUBLE DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 127 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'pri06' and COLUMN_NAME not like '__mo%'; 128 table_name column_name data_type is_nullable 129 pri06 col1 SMALLINT NO 130 pri06 col2 DOUBLE YES 131 show columns from pri06; 132 Field Type Null Key Default Extra Comment 133 col1 SMALLINT(16) NO PRI null 134 col2 DOUBLE(0) YES null 135 drop table pri06; 136 drop table if exists pri07; 137 create table pri07 (col1 decimal, col2 double); 138 insert into pri07 (col1, col2) values (12.213231000021312, -32734928490.3284032); 139 insert into pri07 values (32784234.4234243243243242, 3289323423); 140 select * from pri07; 141 col1 col2 142 12 -3.2734928490328403E10 143 32784234 3.289323423E9 144 alter table pri07 add constraint primary key (col1); 145 show create table pri07; 146 Table Create Table 147 pri07 CREATE TABLE `pri07` (\n`col1` DECIMAL(38,0) NOT NULL,\n`col2` DOUBLE DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 148 show columns from pri07; 149 Field Type Null Key Default Extra Comment 150 col1 DECIMAL128(38) NO PRI null 151 col2 DOUBLE(0) YES null 152 update pri07 set col1 = 1000000 where col2 = 3289323423; 153 update pri07 set col1 = 12.213231000021312 where col2 = 3289323423; 154 Duplicate entry '12' for key 'col1' 155 delete from pri07 where col1 = 12.213231000021312; 156 select * from pri07; 157 col1 col2 158 12 -3.2734928490328403E10 159 1000000 3.289323423E9 160 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'pri07' and COLUMN_NAME not like '__mo%'; 161 table_name column_name data_type is_nullable 162 pri07 col1 DECIMAL128 NO 163 pri07 col2 DOUBLE YES 164 drop table pri07; 165 drop table if exists pri08; 166 create table pri08 (col1 binary, col2 int unsigned); 167 insert into pri08 values ('ewfijew', 372984324); 168 insert into pri08 values ('ew8u3ejkfcwev', 2147483647); 169 select * from pri08; 170 col1 col2 171 ewfijew 372984324 172 ew8u3ejkfcwev 2147483647 173 alter table pri08 add constraint primary key (col1); 174 show create table pri08; 175 Table Create Table 176 pri08 CREATE TABLE `pri08` (\n`col1` BINARY(1) NOT NULL,\n`col2` INT UNSIGNED DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 177 show columns from pri08; 178 Field Type Null Key Default Extra Comment 179 col1 BINARY(1) NO PRI null 180 col2 INT UNSIGNED(32) YES null 181 alter table pri08 add constraint primary key (col2); 182 Multiple primary key defined 183 show create table pri08; 184 Table Create Table 185 pri08 CREATE TABLE `pri08` (\n`col1` BINARY(1) NOT NULL,\n`col2` INT UNSIGNED DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 186 show columns from pri08; 187 Field Type Null Key Default Extra Comment 188 col1 BINARY(1) NO PRI null 189 col2 INT UNSIGNED(32) YES null 190 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'pri08' and COLUMN_NAME not like '__mo%'; 191 table_name column_name data_type is_nullable 192 pri08 col1 BINARY NO 193 pri08 col2 INT UNSIGNED YES 194 drop table pri08; 195 drop table if exists pri09; 196 create table pri09 (col1 binary, col2 int unsigned); 197 insert into pri09 values ('a', 372893243); 198 insert into pri09 values (null, 2147483647); 199 select * from pri09; 200 col1 col2 201 a 372893243 202 null 2147483647 203 alter table pri09 add constraint primary key (col1, col2); 204 constraint violation: Column 'col1' cannot be null 205 show create table pri09; 206 Table Create Table 207 pri09 CREATE TABLE `pri09` (\n`col1` BINARY(1) DEFAULT NULL,\n`col2` INT UNSIGNED DEFAULT NULL\n) 208 show columns from pri09; 209 Field Type Null Key Default Extra Comment 210 col1 BINARY(1) YES null 211 col2 INT UNSIGNED(32) YES null 212 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'pri09' and COLUMN_NAME not like '__mo%'; 213 table_name column_name data_type is_nullable 214 pri09 col1 BINARY YES 215 pri09 col2 INT UNSIGNED YES 216 drop table pri09; 217 drop table if exists pri10; 218 create table pri10 (col1 int, col2 char(1)); 219 insert into pri10 (col1, col2) values (1, 'a'); 220 insert into pri10 values (-2, '*'); 221 select * from pri10; 222 col1 col2 223 1 a 224 -2 * 225 alter table pri10 add constraint primary key (col1, col2); 226 show create table pri10; 227 Table Create Table 228 pri10 CREATE TABLE `pri10` (\n`col1` INT NOT NULL,\n`col2` CHAR(1) NOT NULL,\nPRIMARY KEY (`col1`,`col2`)\n) 229 show columns from pri10; 230 Field Type Null Key Default Extra Comment 231 col1 INT(32) NO PRI null 232 col2 CHAR(1) NO PRI null 233 insert into pri10 (col1, col2) values (1, null); 234 constraint violation: Column 'col2' cannot be null 235 insert into pri10 values (-2, 'p'); 236 insert into pri10 (col1, col2) values (1, 'a'); 237 Duplicate entry ('\(\d\,\w\)'|'\d\w\d{12}') for key '__mo_cpkey_col' 238 select * from pri10; 239 col1 col2 240 1 a 241 -2 * 242 -2 p 243 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'pri10' and COLUMN_NAME not like '__mo%'; 244 table_name column_name data_type is_nullable 245 pri10 col1 INT NO 246 pri10 col2 CHAR NO 247 drop table pri10; 248 drop table if exists pri11; 249 create table pri11 (col1 int primary key , col2 decimal, col3 char); 250 insert into pri11 (col1, col2, col3) values (1, 3289034.3232, 'q'); 251 insert into pri11 values (2, 3829.3232, 'a'); 252 alter table pri11 add constraint primary key (col1, col2); 253 Multiple primary key defined 254 show create table pri11; 255 Table Create Table 256 pri11 CREATE TABLE `pri11` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\n`col3` CHAR(1) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 257 show columns from pri11; 258 Field Type Null Key Default Extra Comment 259 col1 INT(32) NO PRI null 260 col2 DECIMAL128(38) YES null 261 col3 CHAR(1) YES null 262 select * from pri11; 263 col1 col2 col3 264 1 3289034 q 265 2 3829 a 266 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'pri11' and COLUMN_NAME not like '__mo%'; 267 table_name column_name data_type is_nullable 268 pri11 col1 INT NO 269 pri11 col2 DECIMAL128 YES 270 pri11 col3 CHAR YES 271 drop table pri11; 272 drop table if exists temp01; 273 create temporary table temp01 (col1 datetime, col2 blob); 274 insert into temp01 values ('1997-01-13 00:00:00', '342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f'); 275 insert into temp01 values ('2012-01-13 23:23:59', '63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573'); 276 select * from temp01; 277 col1 col2 278 1997-01-13 00:00:00 342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f 279 2012-01-13 23:23:59 63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573 280 alter table temp01 add constraint primary key (col2); 281 alter table for temporary table is not yet implemented 282 select * from temp01; 283 col1 col2 284 1997-01-13 00:00:00 342ewfyuehcdeiuopwu4jo3lekwdfhiu48woi3jrdnefrbwui34f 285 2012-01-13 23:23:59 63298ufh3jcweuiv4h32jhf432ouy4hu3enjwfnwje4n3bj24f34573 286 show create table temp01; 287 Table Create Table 288 temp01 CREATE TABLE `temp01` (\n`col1` DATETIME DEFAULT NULL,\n`col2` BLOB DEFAULT NULL\n) 289 show columns from temp01; 290 Field Type Null Key Default Extra Comment 291 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'temp01' and COLUMN_NAME not like '__mo%'; 292 table_name column_name data_type is_nullable 293 drop table temp01; 294 drop table if exists ex_table_2_1; 295 create external table ex_table_2_1(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double(6,5),num_col11 decimal(38,19))infile{"filepath"='$resources/external_table_file/ex_table_2_1.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 296 alter table ex_table_2_1 add constraint primary key (num_col1, num_col2); 297 You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. error field terminator at line 14 column 109 near " '""' LINES TERMINATED BY '\\n'"; 298 show create table ex_table_2_1; 299 Table Create Table 300 ex_table_2_1 CREATE EXTERNAL TABLE `ex_table_2_1` (\n`num_col1` TINYINT DEFAULT NULL,\n`num_col2` SMALLINT DEFAULT NULL,\n`num_col3` INT DEFAULT NULL,\n`num_col4` BIGINT DEFAULT NULL,\n`num_col5` TINYINT UNSIGNED DEFAULT NULL,\n`num_col6` SMALLINT UNSIGNED DEFAULT NULL,\n`num_col7` INT UNSIGNED DEFAULT NULL,\n`num_col8` BIGINT UNSIGNED DEFAULT NULL,\n`num_col9` FLOAT(5,3) DEFAULT NULL,\n`num_col10` DOUBLE(6,5) DEFAULT NULL,\n`num_col11` DECIMAL(38,19) DEFAULT NULL\n) INFILE{'FILEPATH'='','COMPRESSION'='','FORMAT'='','JSONDATA'=''} FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' 301 select * from ex_table_2_1; 302 internal error: the input value 'abc' is not int8 type for column 0 303 drop table if exists droppri01; 304 create table droppri01 (col1 int primary key , col2 decimal); 305 insert into droppri01 (col1, col2) values (1, 234324234.234242); 306 insert into droppri01 values (32894324,4234294023.4324324234); 307 alter table droppri01 drop primary key; 308 show create table droppri01; 309 Table Create Table 310 droppri01 CREATE TABLE `droppri01` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 311 show columns from droppri01; 312 Field Type Null Key Default Extra Comment 313 col1 INT(32) NO null 314 col2 DECIMAL128(38) YES null 315 insert into droppri01 (col1, col2) values (1, 3489372843); 316 truncate table droppri01; 317 alter table droppri01 add constraint primary key (col2); 318 show create table droppri01; 319 Table Create Table 320 droppri01 CREATE TABLE `droppri01` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) NOT NULL,\nPRIMARY KEY (`col2`)\n) 321 alter table droppri01 drop primary key; 322 show create table droppri01; 323 Table Create Table 324 droppri01 CREATE TABLE `droppri01` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) NOT NULL\n) 325 show columns from droppri01; 326 Field Type Null Key Default Extra Comment 327 col1 INT(32) NO null 328 col2 DECIMAL128(38) NO null 329 select * from droppri01; 330 col1 col2 331 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'droppri01' and COLUMN_NAME not like '__mo%'; 332 table_name column_name data_type is_nullable 333 droppri01 col1 INT NO 334 droppri01 col2 DECIMAL128 NO 335 drop table droppri01; 336 drop table if exists pri01; 337 create table pri01(col1 int, col2 decimal); 338 alter table pri01 add constraint primary key(col1); 339 show create table pri01; 340 Table Create Table 341 pri01 CREATE TABLE `pri01` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 342 alter table pri01 drop primary key; 343 show create table pri01; 344 Table Create Table 345 pri01 CREATE TABLE `pri01` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 346 alter table pri01 add constraint primary key(col2); 347 show create table pri01; 348 Table Create Table 349 pri01 CREATE TABLE `pri01` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) NOT NULL,\nPRIMARY KEY (`col2`)\n) 350 show columns from pri01; 351 Field Type Null Key Default Extra Comment 352 col1 INT(32) NO null 353 col2 DECIMAL128(38) NO PRI null 354 select table_name,COLUMN_NAME, data_type,is_nullable from information_schema.columns where table_name like 'pri01' and COLUMN_NAME not like '__mo%'; 355 table_name column_name data_type is_nullable 356 pri01 col1 INT NO 357 pri01 col2 DECIMAL128 NO 358 drop table pri01; 359 drop table if exists droppri02; 360 create table droppri02 (col1 int auto_increment, col2 decimal, col3 char, col4 varchar not null, col5 float, primary key (col1, col2, col3)); 361 show create table droppri02; 362 Table Create Table 363 droppri02 CREATE TABLE `droppri02` (\n`col1` INT NOT NULL AUTO_INCREMENT,\n`col2` DECIMAL(38,0) NOT NULL,\n`col3` CHAR(1) NOT NULL,\n`col4` VARCHAR(65535) NOT NULL,\n`col5` FLOAT DEFAULT NULL,\nPRIMARY KEY (`col1`,`col2`,`col3`)\n) 364 show columns from droppri02; 365 Field Type Null Key Default Extra Comment 366 col1 INT(32) NO PRI null 367 col2 DECIMAL128(38) NO PRI null 368 col3 CHAR(1) NO PRI null 369 col4 VARCHAR(65535) NO null 370 col5 FLOAT(0) YES null 371 alter table droppri02 drop primary key; 372 show create table droppri02; 373 Table Create Table 374 droppri02 CREATE TABLE `droppri02` (\n`col1` INT NOT NULL AUTO_INCREMENT,\n`col2` DECIMAL(38,0) NOT NULL,\n`col3` CHAR(1) NOT NULL,\n`col4` VARCHAR(65535) NOT NULL,\n`col5` FLOAT DEFAULT NULL\n) 375 show columns from droppri02; 376 Field Type Null Key Default Extra Comment 377 col1 INT(32) NO null 378 col2 DECIMAL128(38) NO null 379 col3 CHAR(1) NO null 380 col4 VARCHAR(65535) NO null 381 col5 FLOAT(0) YES null 382 drop table droppri02; 383 drop table if exists prepare01; 384 create table prepare01(col1 int primary key , col2 char); 385 insert into prepare01 values (1,'a'),(2,'b'),(3,'c'); 386 show create table prepare01; 387 Table Create Table 388 prepare01 CREATE TABLE `prepare01` (\n`col1` INT NOT NULL,\n`col2` CHAR(1) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 389 show columns from prepare01; 390 Field Type Null Key Default Extra Comment 391 col1 INT(32) NO PRI null 392 col2 CHAR(1) YES null 393 prepare s1 from 'alter table prepare01 drop primary key'; 394 execute s1; 395 show create table prepare01; 396 Table Create Table 397 prepare01 CREATE TABLE `prepare01` (\n`col1` INT NOT NULL,\n`col2` CHAR(1) DEFAULT NULL\n) 398 show columns from prepare01; 399 Field Type Null Key Default Extra Comment 400 col1 INT(32) NO null 401 col2 CHAR(1) YES null 402 prepare s2 from 'alter table prepare01 add constraint primary key(col2) '; 403 execute s2; 404 show create table prepare01; 405 Table Create Table 406 prepare01 CREATE TABLE `prepare01` (\n`col1` INT NOT NULL,\n`col2` CHAR(1) NOT NULL,\nPRIMARY KEY (`col2`)\n) 407 show columns from prepare01; 408 Field Type Null Key Default Extra Comment 409 col1 INT(32) NO null 410 col2 CHAR(1) NO PRI null 411 drop table prepare01; 412 drop role if exists role_r1; 413 drop user if exists role_u1; 414 drop table if exists test01; 415 create role role_r1; 416 create user role_u1 identified by '111' default role role_r1; 417 create table test01(col1 int, col2 varchar(10)); 418 insert into test01 values(1, 'ewuijernf'); 419 insert into test01 values(2, 'abscde'); 420 grant create database on account * to role_r1; 421 grant show databases on account * to role_r1; 422 grant connect on account * to role_r1; 423 grant select on table * to role_r1; 424 grant show tables on database * to role_r1; 425 use alter_table_add_drop_primary_key; 426 alter table test01 add constraint primary key(col1); 427 internal error: do not have privilege to execute the statement 428 grant alter table on database * to role_r1; 429 use alter_table_add_drop_primary_key; 430 alter table test01 add constraint primary key(col1); 431 show create table test01; 432 Table Create Table 433 test01 CREATE TABLE `test01` (\n`col1` INT NOT NULL,\n`col2` VARCHAR(10) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 434 show columns from test01; 435 Field Type Null Key Default Extra Comment 436 col1 INT(32) NO PRI null 437 col2 VARCHAR(10) YES null 438 alter table test01 drop primary key; 439 show create table test01; 440 Table Create Table 441 test01 CREATE TABLE `test01` (\n`col1` INT NOT NULL,\n`col2` VARCHAR(10) DEFAULT NULL\n) 442 show columns from test01; 443 Field Type Null Key Default Extra Comment 444 col1 INT(32) NO null 445 col2 VARCHAR(10) YES null 446 show create table test01; 447 Table Create Table 448 test01 CREATE TABLE `test01` (\n`col1` INT NOT NULL,\n`col2` VARCHAR(10) DEFAULT NULL\n) 449 show columns from test01; 450 Field Type Null Key Default Extra Comment 451 col1 INT(32) NO null 452 col2 VARCHAR(10) YES null 453 drop table test01; 454 drop role role_r1; 455 drop user role_u1; 456 drop table if exists mix01; 457 create table mix01 (col1 int, col2 decimal, col3 char, col4 varchar(100)); 458 insert into mix01 (col1, col2, col3, col4) values (1, 2, 'a', 'w3uir34jn2k48ujf4'); 459 insert into mix01 (col1, col2, col3, col4) values (2, 3, 'd', '3289u3ji2dff43'); 460 alter table mix01 modify col1 float after col3, change column col2 col2New double, rename column col3 to newCol3, add constraint primary key(col1); 461 insert into mix01 (col1, col2, col3, col4) values (3, 'w', 37283.323, 'dswhjkfrewr'); 462 Unknown column 'col2' in 'mix01' 463 alter table mix01 add column col5 int after col1, rename column col2new to newnewCol2; 464 select * from mix01; 465 newnewcol2 newcol3 col1 col5 col4 466 2.0 a 1.0 null w3uir34jn2k48ujf4 467 3.0 d 2.0 null 3289u3ji2dff43 468 alter table mix01 rename column col2new to newnewCol2, drop primary key; 469 Unknown column 'col2new' in 'mix01' 470 show create table mix01; 471 Table Create Table 472 mix01 CREATE TABLE `mix01` (\n`newnewcol2` DOUBLE DEFAULT NULL,\n`newcol3` CHAR(1) DEFAULT NULL,\n`col1` FLOAT NOT NULL,\n`col5` INT DEFAULT NULL,\n`col4` VARCHAR(100) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 473 delete from mix01 where newnewcol2 = 2; 474 update mix01 set newnewcol2 = 8290432.324 where newcol3 = 'd'; 475 select * from mix01; 476 newnewcol2 newcol3 col1 col5 col4 477 8290432.324 d 2.0 null 3289u3ji2dff43 478 show create table mix01; 479 Table Create Table 480 mix01 CREATE TABLE `mix01` (\n`newnewcol2` DOUBLE DEFAULT NULL,\n`newcol3` CHAR(1) DEFAULT NULL,\n`col1` FLOAT NOT NULL,\n`col5` INT DEFAULT NULL,\n`col4` VARCHAR(100) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 481 show columns from mix01; 482 Field Type Null Key Default Extra Comment 483 newnewcol2 DOUBLE(0) YES null 484 newcol3 CHAR(1) YES null 485 col1 FLOAT(0) NO PRI null 486 col5 INT(32) YES null 487 col4 VARCHAR(100) YES null 488 drop table mix01; 489 drop table if exists table01; 490 begin; 491 create table table01(col1 int, col2 decimal); 492 insert into table01 values(100,200); 493 insert into table01 values(200,300); 494 alter table table01 add constraint primary key (col2); 495 commit; 496 select * from table01; 497 col1 col2 498 100 200 499 200 300 500 select col1 from table01; 501 col1 502 100 503 200 504 drop table table01; 505 drop table if exists table01; 506 begin; 507 create table table01(col1 int primary key, col2 decimal); 508 insert into table01 values(100,200); 509 insert into table01 values(200,300); 510 alter table table01 drop primary key; 511 commit; 512 select * from table01; 513 col1 col2 514 100 200 515 200 300 516 select col1 from table01; 517 col1 518 100 519 200 520 drop table table01;