Skip to content

Instantly share code, notes, and snippets.

@shner-elmo

shner-elmo/mysql_weird_PK_error.sql

Created March 2, 2023 11:15
Show Gist options
  • Save shner-elmo/6a25e177aa6c26827eb556defb0aebd5 to your computer and use it in GitHub Desktop.
Save shner-elmo/6a25e177aa6c26827eb556defb0aebd5 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL raises a `DUPLICATE KEY ERROR` from two different values
Raw
mysql_weird_PK_error.sql
CREATE TABLE `test` (
`title` varchar(255) PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO test (title) VALUES ('1. Līga'), ('1. liga'); -- raises: Error Code: 1062. Duplicate entry '1. liga' for key 'PRIMARY'
DROP TABLE `test`;
-- ############################################################
CREATE TABLE `test` (
`title` varchar(255) PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;
INSERT INTO test (title) VALUES ('1. Līga'), ('1. liga');
SELECT * FROM test; -- after changing the COLLATE to case_sensitive everything works!
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment