[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. -- The Royal Society for the Prevention of Cruelty to Animals was formed in 1824. The National Society for the Prevention of Cruelty to Children was not formed until 1884. That says something about the British. Please reply to the list; please *don't* CC me.
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