[Maria-discuss] Change case with unique constraint
I can't change the case of a column with a unique index, example below.
MariaDB [test]> show columns in kae; +-------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(2048) | NO | UNI | NULL | | +-------+---------------+------+-----+---------+----------------+ 2 rows in set (0.001 sec)
MariaDB [test]> select * from kae; +----+------+ | id | name | +----+------+ | 1 | test | +----+------+ 1 row in set (0.001 sec)
MariaDB [test]> update kae set name="Test" where id=1; ERROR 1062 (23000): Duplicate entry 'Test' for key 'unique_name' MariaDB [test]>
On Sunday 21 August 2022 at 21:28:13, Keith Edmunds wrote:
MariaDB [test]> update kae set name="Test" where id=1; ERROR 1062 (23000): Duplicate entry 'Test' for key 'unique_name'
Check the collation type for the table:
https://mariadb.com/kb/en/supported-character-sets-and-collations/#collation...
https://mariadb.com/kb/en/setting-character-sets-and-collations/
I suspect the problem you have is that your table uses a case-insensitive collation.
Antony.
Hi, Keith,
Thanks, it's a bug. Reported as https://jira.mariadb.org/browse/MDEV-29345
Until it's fixed, you can as a workaround update your column to something else and then to "Test". Or you can reduce the column length.
On Aug 21, Keith Edmunds wrote:
I can't change the case of a column with a unique index, example below.
MariaDB [test]> show columns in kae; +-------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(2048) | NO | UNI | NULL | | +-------+---------------+------+-----+---------+----------------+ 2 rows in set (0.001 sec)
MariaDB [test]> select * from kae; +----+------+ | id | name | +----+------+ | 1 | test | +----+------+ 1 row in set (0.001 sec)
MariaDB [test]> update kae set name="Test" where id=1; ERROR 1062 (23000): Duplicate entry 'Test' for key 'unique_name' MariaDB [test]>
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Hey,
Was it tested with a non ci collate?
Eliezer
---- Eliezer Croitoru NgTech, Tech Support Mobile: +972-5-28704261 Email: ngtech1ltd@gmail.com Web: https://ngtech.co.il/ My-Tube: https://tube.ngtech.co.il/
-----Original Message----- From: Maria-discuss maria-discuss-bounces+ngtech1ltd=gmail.com@lists.launchpad.net On Behalf Of Sergei Golubchik Sent: Monday, 22 August 2022 9:49 To: Keith Edmunds kae@midnighthax.com Cc: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] Change case with unique constraint
Hi, Keith,
Thanks, it's a bug. Reported as https://jira.mariadb.org/browse/MDEV-29345
Until it's fixed, you can as a workaround update your column to something else and then to "Test". Or you can reduce the column length.
On Aug 21, Keith Edmunds wrote:
I can't change the case of a column with a unique index, example below.
MariaDB [test]> show columns in kae; +-------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(2048) | NO | UNI | NULL | | +-------+---------------+------+-----+---------+----------------+ 2 rows in set (0.001 sec)
MariaDB [test]> select * from kae; +----+------+ | id | name | +----+------+ | 1 | test | +----+------+ 1 row in set (0.001 sec)
MariaDB [test]> update kae set name="Test" where id=1; ERROR 1062 (23000): Duplicate entry 'Test' for key 'unique_name' MariaDB [test]>
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (4)
-
Antony Stone -
Keith Edmunds -
ngtech1ltd＠gmail.com -
Sergei Golubchik