phpmyadmin Question posted in
The official documentation can be found here.

mysql: unique constraint on multiple columns: IMPOSSIBLE to update just caps letter – Phpmyadmin

I’ve a table with unique constrain on 3 columns:

PROBLEM:
Let’s say one row is

col5      col7     col9
lemon    orange   apple

if I try to modify that very same row to (just update to capital letter):

col5     col7     col9
lemon    Orange   apple

I get the error Duplicate entry lemon-Orange-apple. Basically mysql compares the proposed modification, not just to the other rows of the table, but also to itself, the modified row (before the modification).
After some troubleshooting, I believe it’s related to LONGTEXT… but not sure how to solve it…

QUESTION

Is there a way to keep the table/columns case insensitive, but let understand mysql that I’m just modifying a row, so it doesn’t have to compare the row values to itself (the modified row)?

UPDATE (more details)

Is anybody able to reproduce this problem?

Server version: 10.4.17-MariaDB

After running SHOW CREATE TABLE this is the result:

CREATE TABLE `mytable` 
( `post_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'my comment', 
`col1` varchar(255) DEFAULT NULL COMMENT 'unique or NULL', 
 `col2` varchar(255) NOT NULL, 
 `col3` int(11) DEFAULT NULL, 
 `col4` int(11) DEFAULT NULL COMMENT 'CONSTRAIN: Accepts only NULL and post_ID', 
 `col5` longtext NOT NULL, 
 `col6` longtext NOT NULL, 
 `col7` longtext NOT NULL, 
 `col8` longtext NOT NULL, 
 `col9` longtext NOT NULL, 
 `col10` longtext NOT NULL, 
 PRIMARY KEY (`post_ID`), UNIQUE KEY `col1` (`col1`), 
 UNIQUE KEY `col5` (`col5`(255),`col7`(255),`col9`(255)) USING HASH COMMENT '!!!These columns together MUST be unique!!!', 
 KEY `key_1` (`col3`), KEY `key_2` (`col4`) ) 
 ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8mb4

Insert test data:

INSERT INTO `mytable` 
(`post_ID`, `col1`, `col2`, `col3`, `col4`, `col5`, `col6`, `col7`, `col8`, `col9`, `col10`) 
 VALUES (NULL, NULL, '', NULL, NULL, 'apple', '', 'orange', '', 'apple', '');

Try to Update orange to Orange (just by typing the word Orange into phpMyAdmin field):

UPDATE `mytable` SET `col7` = 'Orange' WHERE `mytable`.`post_ID` = 120

Error:

#1062 - Duplicate entry 'apple-Orange-apple' for key 'col5'

TRIED SOLUTIONS

I tried to modify collation to utf8mb4_bin or latin7_general_cs (as proposed on other questions) for CaSe sEnSiTivE comparisons; it solves the problem of modify only cApS, but it doesn’t work for me because all searches became CaSe sEnSiTivE (obviously) and when I search the table for "orange" it will not find "Orange".

Tried also to alternate make table utf8mb4_bin and columns utf8mb4_general_ci (and viceversa), but with no luck. Same problems.
After some troubleshooting, I believe it’s related to LONGTEXT… but not sure how to solve it…

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    [SOLVED!!]

    By luck and chance, problem is solved!

    Not sure how, but these are the steps I did:

    1. Following Garr Godfrey's comment ("...if they are LONGTEXT, mysql requires a LENGTH to be set on the index") I added length (1000) to the index.

    [at this step still not working, but useful for next steps]

    1. Brute/Force Restart the server (actually the battery died :o !!)

    2. in phpMyAdmin: Optimize Table (in the hope that this was rebuilding the index)

    TA DAAA! Now we are back on business : ) !

    Thanks to everybody for your support!


  2. 0

    MySQL 8.0.13 supports functional indexes. So, you can do what you want using a unique index:

    create unique index idx_t_col1_col2_col3 on t( (convert(col1 using binary)), (convert(col2 using binary)), (convert(col3 using binary)) )

    In older versions of 8.0, you can mimic this by creating computed columns and then creating an index on those columns.

    Here is a simpler example using only one column.

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

Back To Top
Search