Help Center/ Data Warehouse Service / More Documents/ Tool Guide (Kuala Lumpur Region)/ DSC/ Migrating MySQL Syntax/ Indexes/ Unique Indexes
Updated on 2025-10-14 GMT+08:00
View PDF
Share

Unique Indexes

DWS does not support the combination of unique indexes (constraints) and primary key constraints. DSC will perform adaptation based on DWS features during migration.

If MySQL unique indexes (constraints) and primary key constraints are used together during migration, OLAP distribution keys may become unavailable. Therefore, this scenario is not supported by DSC.

  1. For a unique inline index, if the primary key index and the unique index are the same column, DSC will remove the unique index during migration.

    Input

    1
2
3
4
5
6
    		 
    CREATE TABLE IF NOT EXISTS `public`.`runoob_dataType_test`
(
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(128) NOT NULL,
  UNIQUE (id ASC)
);

    Output

    1
2
3
4
5
6
7
8
    		 
    CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"
(
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(128) NOT NULL
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("id");
  2. If there is a unique index created by ALTER TABLE, DSC will convert it to a normal index based on DWS features.

    Input

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
    		 
    CREATE TABLE IF NOT EXISTS `public`.`runoob_alter_test`(
    `dataType1` int,
    `dataType2` FLOAT(10,2),
    `dataType3` DOUBLE(20,8)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE runoob_alter_test ADD UNIQUE idx_runoob_alter_test_datatype1(dataType1);
ALTER TABLE runoob_alter_test ADD UNIQUE INDEX idx_runoob_alter_test_datatype1(dataType2);
ALTER TABLE runoob_alter_test ADD UNIQUE KEY idx_runoob_alter_test_datatype1(dataType3);

CREATE TABLE IF NOT EXISTS `public`.`runoob_alter_test`(
    `dataType1` int,
    `dataType2` FLOAT(10,2),
    `dataType3` DOUBLE(20,8),
    `dataType4` TEXT NOT NULL,
    `dataType5` YEAR NOT NULL DEFAULT '2018',
    `dataType6` DATETIME NOT NULL DEFAULT '2018-10-12 15:27:33.999999'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE runoob_alter_test ADD CONSTRAINT UNIQUE idx_runoob_alter_test_datatype1(dataType1);
ALTER TABLE runoob_alter_test ADD CONSTRAINT UNIQUE INDEX idx_runoob_alter_test_datatype2(dataType2);
ALTER TABLE runoob_alter_test ADD CONSTRAINT UNIQUE KEY idx_runoob_alter_test_datatype3(dataType3);
ALTER TABLE runoob_alter_test ADD CONSTRAINT constraint_dataType UNIQUE idx_runoob_alter_test_datatype4(dataType4);
ALTER TABLE runoob_alter_test ADD CONSTRAINT constraint_dataType UNIQUE INDEX idx_runoob_alter_test_datatype5(dataType5);
ALTER TABLE runoob_alter_test ADD CONSTRAINT constraint_dataType UNIQUE KEY idx_runoob_alter_test_datatype6(dataType6);

    Output

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
    		 
    CREATE TABLE IF NOT EXISTS "public"."runoob_alter_test"
(
  "datatype1" INTEGER,
  "datatype2" REAL,
  "datatype3" DOUBLE PRECISION
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("datatype1");

CREATE INDEX "idx_runoob_alter_test_datatype1" ON "public"."runoob_alter_test" ("datatype1");
CREATE INDEX "idx_runoob_alter_test_datatype1" ON "public"."runoob_alter_test" ("datatype2");
CREATE INDEX "idx_runoob_alter_test_datatype1" ON "public"."runoob_alter_test" ("datatype3");

CREATE TABLE IF NOT EXISTS "public"."runoob_alter_test"
(
  "datatype1" INTEGER,
  "datatype2" REAL,
  "datatype3" DOUBLE PRECISION,
  "datatype4" TEXT NOT NULL,
  "datatype5" SMALLINT NOT NULL DEFAULT '2018',
  "datatype6" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT '2018-10-12 15:27:33.999999'
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("datatype1");

CREATE INDEX "idx_runoob_alter_test_datatype1" ON "public"."runoob_alter_test" ("datatype1");
CREATE INDEX "idx_runoob_alter_test_datatype2" ON "public"."runoob_alter_test" ("datatype2");
CREATE INDEX "idx_runoob_alter_test_datatype3" ON "public"."runoob_alter_test" ("datatype3");
CREATE INDEX "idx_runoob_alter_test_datatype4" ON "public"."runoob_alter_test" ("datatype4");
CREATE INDEX "idx_runoob_alter_test_datatype5" ON "public"."runoob_alter_test" ("datatype5");
CREATE INDEX "idx_runoob_alter_test_datatype6" ON "public"."runoob_alter_test" ("datatype6");
  3. If there is a unique index created by CREATE INDEX, DSC will convert it to a normal index based on DWS features.

    Input

    1
2
3
4
5
6
7
8
9
    		 
    CREATE TABLE `public`.`test_index_table01` (
	`TABLE01_ID` INT(11) NOT NULL,
	`TABLE01_THEME` VARCHAR(100) NULL DEFAULT NULL,
	`AUTHOR_NAME` CHAR(10) NULL DEFAULT NULL,
	`AUTHOR_ID` INT(11) NULL DEFAULT NULL,
	`CREATE_TIME` INT NULL DEFAULT NULL,
	PRIMARY KEY(`TABLE01_ID`)
);
CREATE UNIQUE INDEX AUTHOR_INDEX ON `test_index_table01`(AUTHOR_ID);

    Output

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    		 
    CREATE TABLE "public"."test_index_table01"
(
  "table01_id" INTEGER NOT NULL,
  "table01_theme" VARCHAR(400) DEFAULT NULL,
  "author_name" CHAR(40) DEFAULT NULL,
  "author_id" INTEGER DEFAULT NULL,
  "create_time" INTEGER DEFAULT NULL,
  PRIMARY KEY ("table01_id")
)
  WITH ( ORIENTATION = ROW, COMPRESSION = NO )
  NOCOMPRESS
  DISTRIBUTE BY HASH ("table01_id");
CREATE INDEX "author_index" ON "public"."test_index_table01" ("author_id");
  4. If CREATE TABLE has multiple unique indexes, DSC will convert all unique indexes to common indexes based on DWS features during migration.

    Input

    1
2
3
4
5
6
7
8
9
    		 
    CREATE TABLE `public`.`test_index_table01` (
	`TABLE01_ID` INT(11) NOT NULL,
	`TABLE01_THEME` VARCHAR(100) NULL DEFAULT NULL,
	`AUTHOR_NAME` CHAR(10) NULL DEFAULT NULL,
	`AUTHOR_ID` INT(11) NULL DEFAULT NULL,
	`CREATE_TIME` INT NULL DEFAULT NULL,
	UNIQUE(`TABLE01_ID`),
	UNIQUE(`AUTHOR_ID`)
);

    Output

    1
2
3
4
5
6
7
8
9
    		 
    CREATE TABLE "public"."test_index_table01" (
  "table01_id" INTEGER NOT NULL,
  "table01_theme" VARCHAR(400) DEFAULT NULL,
  "author_name" CHAR(40) DEFAULT NULL,
  "author_id" INTEGER DEFAULT NULL,
  "create_time" INTEGER DEFAULT NULL
) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("table01_id");
CREATE INDEX "idx_test_index_table01_table01_id" ON "public"."test_index_table01"("TABLE01_ID");
CREATE INDEX "idx_test_index_table01_author_id" ON "public"."test_index_table01"("AUTHOR_ID");
  5. If CREATE TABLE has a unique index but does not have a primary key index, DSC retains the unique index based on DWS features during migration.

    Input

    1
2
3
4
5
6
7
8
    		 
    CREATE TABLE `public`.`test_index_table01` (
	`TABLE01_ID` INT(11) NOT NULL,
	`TABLE01_THEME` VARCHAR(100) NULL DEFAULT NULL,
	`AUTHOR_NAME` CHAR(10) NULL DEFAULT NULL,
	`AUTHOR_ID` INT(11) NULL DEFAULT NULL,
	`CREATE_TIME` INT NULL DEFAULT NULL,
	UNIQUE(`AUTHOR_ID`)
);

    Output

    1
2
3
4
5
6
7
8
    		 
    CREATE TABLE "public"."test_index_table01" (
  "table01_id" INTEGER NOT NULL,
  "table01_theme" VARCHAR(400) DEFAULT NULL,
  "author_name" CHAR(40) DEFAULT NULL,
  "author_id" INTEGER DEFAULT NULL,
  "create_time" INTEGER DEFAULT NULL,
  UNIQUE ("author_id")
) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("author_id");

  6. If CREATE TABLE has a primary key index, DSC converts all unique indexes to common indexes based on DWS features during migration.

    Input

    1
2
3
4
5
6
7
8
9
    		 
    CREATE TABLE `public`.`test_index_table01` (
	`TABLE01_ID` INT(11) NOT NULL,
	`TABLE01_THEME` VARCHAR(100) NULL DEFAULT NULL,
	`AUTHOR_NAME` CHAR(10) NULL DEFAULT NULL,
	`AUTHOR_ID` INT(11) NULL DEFAULT NULL,
	`CREATE_TIME` INT NULL DEFAULT NULL,
	PRIMARY KEY(`TABLE01_ID`),
	UNIQUE(`AUTHOR_ID`)
);

    Output

    1
2
3
4
5
6
7
8
9
    		 
    CREATE TABLE "public"."test_index_table01" (
  "table01_id" INTEGER NOT NULL,
  "table01_theme" VARCHAR(400) DEFAULT NULL,
  "author_name" CHAR(40) DEFAULT NULL,
  "author_id" INTEGER DEFAULT NULL,
  "create_time" INTEGER DEFAULT NULL,
  PRIMARY KEY ("table01_id")
) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("table01_id");
CREATE INDEX "idx_test_index_table01_author_id" ON "public"."test_index_table01"("AUTHOR_ID");
Parent topic: Indexes