CODEWITHSUNDEEP
mysql
Martyn
Martyn

Reputation: 6383

Do I have a duplicate key violation in MySQL?

Well, yes I do. I am receiving the following error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-684-Answer' for key 'user_id' (SQL: insert into `votes` (`votable_id`, `votable_type`, `user_id`, `updated_at`, `created_at`) values (684, Answer, 1, 2016-03-17 12:16:53, 2016-03-17 12:16:53))

That would suggest I have a UNIQUE constraint on the user_id column? Looking at the DESCRIBE votes; I'm not sure whether this indicates that ot not. It shouldn't have though, one user should be able to vote many times:

mysql> describe votes;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id      | int(11)      | NO   | MUL | NULL    |                |
| votable_id   | int(11)      | NO   |     | NULL    |                |
| votable_type | varchar(255) | NO   |     | NULL    |                |
| value        | int(11)      | NO   |     | NULL    |                |
| created_at   | datetime     | NO   |     | NULL    |                |
| updated_at   | datetime     | NO   |     | NULL    |                |
| deleted_at   | datetime     | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+



mysql> show indexes from votes where column_name = 'user_id';
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| votes |          0 | user_id  |            1 | user_id     | A         |          51 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Does the above indicate that UNIQUE property is set? I'm looking at the migrations file and I can't see where the UNIQUE property was assigned. Perhaps someone put in manually.

Upvotes: 0

Views: 92

Answers (2)

Zamrony P. Juhara
Zamrony P. Juhara

Reputation: 5262

Non_unique = 0. It means it cannot contain duplicates. See SHOW INDEX documentation.

Upvotes: 1

aroy
aroy

Reputation: 482

This shows that there is an index user_id in your table.

you can use following code to see index that will list out all the indexes :

SHOW INDEX FROM votes;

if it doesnot solve your problem remove the unique index and recreate it again.

Upvotes: 1

Related Questions