Unique Absolute MySQL Value-CodePudding

I have a database with the integer id that can be positive or negative. However a positive and negative integer with the same absolute value can't exist.

Is it possible to set up a key that would ensure id's absolute value uniqueness?

The following attempt returns a syntax error:

ALTER TABLE `table` ADD KEY(ABS(`id`));

CodePudding user response：

Add virtual generated column into the table structure and uniquely index it.

ALTER TABLE tablename
    ADD COLUMN abs_id INT AS (ABS(id)) VIRTUAL,
    ADD UNIQUE INDEX (abs_id));

DEMO