Error Code : 1503: A Unique Index must include all columns in the table. How can I partition the table when a unique index is present?

Question

Column Definitions

The ID column is the primary key with auto-increment that we use to maintain the pool range.

Serial is unique and needs to be unique even after partitioning.

The status of a product is an enum with a value of A or U.

Problem:

I want to partition the table on the basis of status. Most SQL queries are based on a primary key or serial number with status A. When I am trying to create a partition using the query below, it is not working.

ALTER TABLE product
PARTITION BY LIST COLUMNS(status)
(
   PARTITION used VALUES IN ('U'),
   PARTITION used VALUES IN ('A')
);
Error Code : 1503: A Unique Index must include all columns in the table

Can anyone tell me the right approach to solving this problem?

Answer 1

You cannot use ENUM - this datatype is not allowed in the partitioning expression. Use CHAR(1) with CHECK constraint instead. MySQL 8.0.16 or newer needed. If your version is lower then check this condition in the triggers.

You cannot define serial as UNIQUE with according index because any unique index must include all columns which are used in the partitioning expression. Use triggers instead.

Primary key must be composite - it must include both status column (see above) and id column as a prefix (needed for autoincrementing). If you need to check id uniqueness separately then expand the triggers codes accordingly.

Sample

Create a table:

CREATE TABLE product (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    `serial` VARCHAR(16) NOT NULL,
    status CHAR(1) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, status),
    CHECK (status IN ('A', 'U'))
);

Create triggers:

CREATE TRIGGER tr1
BEFORE INSERT ON product
FOR EACH ROW 
BEGIN
    IF EXISTS ( SELECT NULL 
                FROM product 
                WHERE `serial` = NEW.`serial`
                ) THEN 
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'The column ''serial'' must be unique.';
    END IF;
END

CREATE TRIGGER tr2
BEFORE UPDATE ON product
FOR EACH ROW 
BEGIN
    IF EXISTS ( SELECT NULL
                FROM product
                WHERE `serial` = NEW.`serial`
                  AND OLD.`serial` <> NEW.`serial`
                ) THEN 
        SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'The column ''serial'' must be unique.';
    END IF;
END

Alter the table, create partitions:

ALTER TABLE product
PARTITION BY LIST COLUMNS(status)
(
   PARTITION used VALUES IN ('U'),
   PARTITION not_used VALUES IN ('A')
);

Insert legal rows:

INSERT INTO product (`serial`, status) VALUES ('ABC', 'A');
INSERT INTO product VALUES (DEFAULT, 'DEF', 'U', DEFAULT);

Insert duplicate by serial - error:

INSERT INTO product (`serial`, status) VALUES ('DEF', 'A');

The column 'serial' must be unique.

Insert invalid status value - error:

INSERT INTO product (`serial`, status) VALUES ('GHI', 'X');

Check constraint 'product_chk_1' is violated.

Update serial to existing value - error:

UPDATE product SET `serial` = 'ABC' WHERE `serial` = 'DEF';

The column 'serial' must be unique.

Update with serial unchanged - allowed by the trigger:

UPDATE product SET created_at = NOW() + INTERVAL 1 DAY WHERE status = 'A';

See table content:

SELECT * FROM product;

id	serial	status	created_at
2	DEF	U	2023-12-21 06:07:14
1	ABC	A	2023-12-22 06:07:14

See single partition content:

SELECT * FROM product PARTITION (used);

id	serial	status	created_at
2	DEF	U	2023-12-21 06:07:14

fiddle