970_90

Thursday, 8 January 2015

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY

Following are few reasons with solutions:

Reason 1:
ID is auto-increment and primary key. id is auto filed when an record is insert.
When we try to insert record with same id which is already exist in table, then such type of error comes.
SOLUTION:
So, write SQL in following ways.
INSERT INTO users (name) VALUES ('name1'); //skip the id field which is auto-increment


Reason 2:
When table is used to frequent insertion of records, like we are storing db logs for each action. Sometimes, it have thousand of thousands records, and field(like id) limit cross the limit. SOLUTION: Increase the limit of id field.
ALTER TABLE `users` CHANGE `id` `id` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT; 
Reason 3:
When table is used to frequent insertion of records, like we are storing db logs for each action.Sometimes, it have thousand of thousands records, and table goes to overhead.
SOLUTION: Execute following query
OPTIMIZE TABLE `users`

No comments:

Post a Comment