Table of Contents
When inserting broker-level trading data for Taiwan stocks into MySQL, an error occurs indicating duplicate entries for the primary key. Possible causes include null values, case-insensitive collation, and using float columns as primary keys. These issues can lead to unexpected insertion errors despite data appearing unique.
When I download broker-level trading data for Taiwan stocks from the internet and use Python to insert it into the MySQL database, MySQL returns the following error message:
1062 – Duplicate entry ‘2022-02-10-1712-116M-29.65’ for key ‘PRIMARY
Even after thorough verification using Pandas, it is still unclear where the duplicate data is. Upon closer inspection, there are several possible reasons for this issue, which are enumerated below.
Null Values
In theory, Primary Keys are not allowed to be Null, so Null values may lead to insertion errors.
Collation
Some databases use case-insensitive encoding, which might be the cause of errors when writing to the database.
Using Float Column as Primary Key
According to should we use float as primary key in sql-server, databases usually do not prohibit using a float column as a primary key, but it is strongly discouraged.
