MYSQL: unique identifier for the partition table
We are using a table which has the following schema: -
CREATE TABLE `user_subscription` (
`ID` varchar(40) NOT NULL,
`COL1` varchar(40) NOT NULL,
`COL2` varchar(30) NOT NULL,
`COL3` datetime NOT NULL,
`COL4` datetime NOT NULL,
`ARCHIVE` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
)
Now we wanted to make a section in the ARCHIVE column. ARCHIVE can have only 2 values 0 or 1 and 2 sections.
In fact, in our case, we use splitting as an archival process. To make a section, we need to make the ARCHIVE column part of the primary key. But the problem here is that 2 rows can have the same ID with different ARCHIVE column value. This is actually not the main problem for us, since the two lines will be in different sections. The problem is that we will update the archive column value of one of them with the other to move one of the rows into the archive section, and then prevent us from updating the record giving "Duplicate Error".
Can anyone help in this regard?
source to share
Unfortunately,
A
UNIQUE INDEX
(orPRIMARY KEY
) must include all columns in the table splitting function
and since MySQL does not support check constraints, the only ugly workaround I can think of is manually enforcing uniqueness, although triggers are:
CREATE TABLE t (
id INT NOT NULL,
archived TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id, archived), -- required by MySQL limitation on partitioning
)
PARTITION BY LIST(archived) (
PARTITION pActive VALUES IN (0),
PARTITION pArchived VALUES IN (1)
);
CREATE TRIGGER tInsert
BEFORE INSERT ON t FOR EACH ROW
CALL checkUnique(NEW.id);
CREATE TRIGGER tUpdate
BEFORE UPDATE ON t FOR EACH ROW
CALL checkUnique(NEW.id);
DELIMITER //
CREATE PROCEDURE checkUnique(pId INT)
BEGIN
DECLARE flag INT;
DECLARE message VARCHAR(50);
SELECT id INTO flag FROM t WHERE id = pId;
IF flag IS NOT NULL THEN
-- the below tries to mimic the error raised
-- by a regular UNIQUE constraint violation
SET message = CONCAT("Duplicate entry '", pId, "'");
SIGNAL SQLSTATE "23000" SET
MYSQL_ERRNO = 1062,
MESSAGE_TEXT = message,
COLUMN_NAME = "id";
END IF;
END //
( fiddle )
MySQL's limitations on partitioning are so unfortunate (notably the lack of foreign key support), I would advise not to fully use it until the table gets so large that it becomes an actual problem.
source to share