github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/primary_key_transfer.sql (about)

     1  drop table if exists test1;
     2  drop table if exists test2;
     3  drop table if exists test3;
     4  
     5  create table test1 (
     6  id int(10) primary key,
     7  uname varchar(20) unique key,
     8  sex varchar(10),
     9  birth date,
    10  department varchar(20),
    11  address varchar (50)
    12  );
    13  
    14  INSERT INTO test1 VALUES (7369,'SMITH', 'Male', '1980-12-17','ACCOUNTING','NEW YORK');
    15  INSERT INTO test1 VALUES (7499,'ALLEN', 'Female', '1981-02-20','RESEARCH','DALLAS');
    16  INSERT INTO test1 VALUES (7521,'WARD', 'Male', '1981-02-22','SALES','CHICAGO');
    17  INSERT INTO test1 VALUES (7839,'KING', 'Female', '1981-11-17','ACCOUNTING','NEW YORK');
    18  INSERT INTO test1 VALUES (7844,'TURNER', 'Male', '1981-09-08','RESEARCH', 'DALLAS');
    19  INSERT INTO test1 VALUES (7902,'FORD', 'Male', '1981-12-03','ACCOUNTING','NEW YORK');
    20  
    21  select * from test1;
    22  update test1 set id = 1200 where uname = 'TURNER';
    23  drop table test1;
    24  
    25  
    26  create table test2 (
    27  id int(10),
    28  uname varchar(20)  unique key,
    29  sex varchar(10),
    30  birth date,
    31  department varchar(20),
    32  address varchar (50),
    33  primary key(id)
    34  );
    35  
    36  INSERT INTO test2 VALUES (7369,'SMITH', 'Male', '1980-12-17','ACCOUNTING','NEW YORK');
    37  INSERT INTO test2 VALUES (7499,'ALLEN', 'Female', '1981-02-20','RESEARCH','DALLAS');
    38  INSERT INTO test2 VALUES (7521,'WARD', 'Male', '1981-02-22','SALES','CHICAGO');
    39  INSERT INTO test2 VALUES (7839,'KING', 'Female', '1981-11-17','ACCOUNTING','NEW YORK');
    40  INSERT INTO test2 VALUES (7844,'TURNER', 'Male', '1981-09-08','RESEARCH', 'DALLAS');
    41  INSERT INTO test2 VALUES (7902,'FORD', 'Male', '1981-12-03','ACCOUNTING','NEW YORK');
    42  
    43  update test2 set id = 1200 where uname = 'TURNER';
    44  select * from test2;
    45  drop table test2;
    46  
    47  create table test3 (
    48  id int unsigned,
    49  uname varchar(20),
    50  sex varchar(10),
    51  birth date unique key,
    52  department varchar(20),
    53  address varchar (50),
    54  primary key(id,uname)
    55  );
    56  
    57  INSERT INTO test3 VALUES (7369,'SMITH', 'Male', '1980-12-17','ACCOUNTING','NEW YORK');
    58  INSERT INTO test3 VALUES (7499,'ALLEN', 'Female', '1981-02-20','RESEARCH','DALLAS');
    59  INSERT INTO test3 VALUES (7521,'WARD', 'Male', '1981-02-22','SALES','CHICAGO');
    60  INSERT INTO test3 VALUES (7839,'KING', 'Female', '1981-11-17','ACCOUNTING','NEW YORK');
    61  INSERT INTO test3 VALUES (7844,'TURNER', 'Male', '1981-09-08','RESEARCH', 'DALLAS');
    62  INSERT INTO test3 VALUES (7902,'FORD', 'Male', '1981-12-03','ACCOUNTING','NEW YORK');
    63  update test3 set id = 1200 where uname = 'TURNER';
    64  select * from test3;
    65  drop table test3;