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

     1  -- Attempt to create a composite unique key with VARCHAR and DATETIME, but with duplicates
     2  DROP TABLE IF EXISTS t0;
     3  CREATE TABLE t0(
     4      col1 VARCHAR(255), 
     5      col2 DATETIME, 
     6      col3 DECIMAL(10,2)
     7  );
     8  
     9  -- Inserting duplicate combination of col1 and col2
    10  INSERT INTO t0 VALUES ('test', '2023-04-01 12:00:00', 123.45);
    11  INSERT INTO t0 VALUES ('test', '2023-04-01 12:00:00', 678.90);
    12  SELECT COUNT(*) FROM t0;
    13  
    14  -- This next statement will fail because there is a duplicate value for (col1, col2)
    15  ALTER TABLE t0 ADD UNIQUE KEY `unique_compound_key`(col1, col2) COMMENT 'Unique constraint for VARCHAR and DATETIME';
    16  
    17  -- If the above statement would have succeeded, you would see information about the unique index
    18  SHOW INDEX FROM t0;
    19  SELECT COUNT(*) FROM t0;
    20  
    21  DROP TABLE t0;
    22  
    23  -- Attempt to create a composite unique key with DECIMAL and DATE, but with duplicates
    24  DROP TABLE IF EXISTS t1;
    25  CREATE TABLE t1(
    26      col1 DECIMAL(5,2), 
    27      col2 DATETIME, 
    28      col3 int
    29  );
    30  
    31  INSERT INTO t1 VALUES (10.55, '2023-04-01', 100);
    32  INSERT INTO t1 VALUES (10.55, '2023-04-01', 200);
    33  SELECT COUNT(*) FROM t1;
    34  
    35  -- This next statement will fail due to the duplicate values for (col1, col2)
    36  ALTER TABLE t1 ADD UNIQUE KEY `unique_decimal_date_key`(col1, col2) COMMENT 'Unique constraint for DECIMAL and DATE';
    37  
    38  -- If the ALTER TABLE statement succeeded, this would show the unique indexes
    39  SHOW INDEX FROM t1;
    40  SELECT COUNT(*) FROM t1;
    41  
    42  DROP TABLE t1;
    43  
    44  DROP TABLE IF EXISTS t3;
    45  create table t3 (col1 bigint primary key,col2 char(25), col3 float, col4 char(50), key num_id(col4));
    46  insert into t3 values (1,'',20.23,'5678'),(2,'13873458290',100.00,'23');
    47  insert into t3 values (67834,'13456789872',20.23,'5678'),(56473,'',100.00,'5678');
    48  ALTER TABLE t3 ADD UNIQUE KEY `unique_empty_char`(col2) COMMENT 'Unique constraint for empty char';
    49  DROP TABLE t3;