CODEWITHSUNDEEP
mysqlduplicates
Thiagarajan Hariharan
Thiagarajan Hariharan

Reputation: 466

MySql complains about 'Duplicate entry' though unique data is being entered

I have this table in a mysql database:

mysql> show columns from wifi_network_config_auth_algorithm;
+------------------------+-------------+------+-----+---------+-------+
| Field                  | Type        | Null | Key | Default | Extra |
+------------------------+-------------+------+-----+---------+-------+
| wifi_network_config_id | int(11)     | NO   | PRI | NULL    |       | 
| auth_algorithm         | varchar(50) | NO   | PRI | NULL    |       | 
+------------------------+-------------+------+-----+---------+-------+

The table was created using liquibase. Table creation itself does not define primary keys. Following table creation, the following liquibase command is used to set the combination of 2 columns as the primary key:

<addPrimaryKey tableName="wifi_network_config_auth_algorithm"
        columnNames="wifi_network_config_id,auth_algorithm"
        constraintName="pk_wifi_network_config_auth_algorithm"/>

Now, when you execute this to load data into this table:

LOCK TABLES `wifi_network_config_auth_algorithm` WRITE;
INSERT INTO `wifi_network_config_auth_algorithm` VALUES
(1, 'OPEN'),
(1, 'SHARED'),
(2, 'SHARED'),
(2, 'LEAP');
UNLOCK TABLES;

You get:

ERROR 1062 (23000): Duplicate entry '1' for key 2

Since the combination of both columns is the primary key, so the combination of the data in the two columns should be unique - which is true for the data shown above. Why is mysql complaining?

Also, it's complaining that the value '1' is being assigned to key 2, which does not make sense. I changed the INSERT to:

INSERT INTO `wifi_network_config_auth_algorithm` (`wifi_network_config_id`, `auth_algorithm`)

but it did not make any difference - same complaint from mysql.

Thanks, Hari

Upvotes: 0

Views: 2072

Answers (2)

Thiagarajan Hariharan
Thiagarajan Hariharan

Reputation: 466

Figured out the issue with @kbenson's help. The answer is in the comments for the original question.

Upvotes: 1

JYelton
JYelton

Reputation: 36512

This works for me. Is the table empty or missing these values before you execute the insert statement?

If I run this example twice I get an error message:

Duplicate entry '1-OPEN' for key 'PRIMARY'

Calling DELETE FROM wifi_network_config_auth_algorithm first avoids that.

Otherwise there must be something wrong with the way the primary key was created, since your error message differs.

I created a test table using the following:

CREATE TABLE `test` (
          `wifi` int(11) NOT NULL,
          `auth` varchar(50) NOT NULL,
          PRIMARY KEY (`wifi`,`auth`)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Upvotes: 1

Related Questions