Question 308738 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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?

enter image description here

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    If serial is unique and unlikely to ever change, then get rid of id and have a non-partitioned table with

    PRIMARY KEY(serial)

    (Please explain "maintain the pool range"; maybe id has some use.)

    "want to partition the table on the basis of status". Instead, consider using composite indexes such as

    INDEX(status, serial)
INDEX(status, created_at)

    With those, WHERE status = 'U' ORDER BY created_at DESC will run as fast or faster without partitioning.

    And as mentioned by comments, this may be useful in some cases:

    INDEX(serial, status)

    "selecting 15000 rows" — What does the client do with so many rows? I would expect it to choke.

    You have the date, what is the id for?

    When walking through a large table, "remember where you left off". This can be done even if the PK is composite. Let’s discuss further when I see the various queries you need. (It may involve a composite PRIMARY KEY that is designed for performance of those large scans.)

    More on "left off" in my blog pm Pagination

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search