CODEWITHSUNDEEP
mysqlsql-insert
jmc1690
jmc1690

Reputation: 95

Duplicate entry '0' for key 'PRIMARY'

I don't understand why I'm getting this error when trying to populate this table. There is nothing in the table at the moment so I don't understand why there would be a duplicate...

This is the code I'm using:

INSERT INTO Suppliers
(supp_id,company_name,town,phone)
Values
("ADT217","AdTec","Birmingham","0121-368-1597"),
("CPS533","CPS","Maidenhead","01382-893715"),
("FCL162","ForComp Ltd","Nottingham","01489-133722"),
("KBC355","KBC Computers","Glasgow","0141-321-1497");

suppliers table...

CREATE TABLE suppliers(
    supp_id int NOT NULL,
    company_name character(15) NOT NULL,
    town character(15)
    phone character(15)
primary key(supp_id)
);

Upvotes: 1

Views: 12276

Answers (3)

sachin kumar
sachin kumar

Reputation: 1

In Wordpress, when we clone the website, the media and user roles are not working. The error is as below:

WordPress database error Duplicate entry '0' for key 'PRIMARY' for query

INSERT INTO `wp_334_actionscheduler_logs` 
       (`action_id`, `message`, `log_date_gmt`, `log_date_local`) 
VALUES (0, 'action complete via WP Cron', '2021-02-17 05:29:40', 
        '2021-02-17 05:29:40')

made by

do_action_ref_array('action_scheduler_run_queue'), 
WP_Hook->do_action, 
WP_Hook->apply_filters, 
ActionScheduler_QueueRunner->run, 
ActionScheduler_QueueRunner->do_batch, 
ActionScheduler_Abstract_QueueRunner->process_action, 
do_action('action_scheduler_after_execute'), 
WP_Hook->do_action,
WP_Hook->apply_filters, 
ActionScheduler_Logger->log_completed_action, 
ActionScheduler_DBLogger->log

Upvotes: 0

Gevorg M
Gevorg M

Reputation: 51

With your table you can get the error like "Incorrect Integer Value", but depending on MySQL server configuration it can do conversion(string->int) automatically for your query string must become "0" as result of this it makes 2 rows with 0 as supp_id and get error Duplicate entry '0' for key 'PRIMARY'. I guess you are using InnoDB as table type, in this case query will run as transaction and it will rollback after first error(for this example it will be second row).

DROP TABLE suppliers; -- Will drop your old table
CREATE TABLE suppliers(
supp_id varchar(30) NULL, -- You can set length as you wish
company_name character(15) NOT NULL,
town character(15),
phone character(15),
primary key(supp_id)
);

INSERT INTO Suppliers
(supp_id,company_name,town,phone)
Values
("ADT217","AdTec","Birmingham","0121-368-1597"),
("CPS533","CPS","Maidenhead","01382-893715"),
("FCL162","ForComp Ltd","Nottingham","01489-133722"),
("KBC355","KBC Computers","Glasgow","0141-321-1497");

After changing type insert will work without problems.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

This occurs when you have a primary key but do not give it an initialization value. The insert itself is causing the duplication.

In your case, two possibilities come to mind:

  1. supp_id is the primary key and declared as a number. In older versions of MySQL, I think the string values get silently converted to numbers. Because the leading characters are letters, the value is 0.

  2. You have another id field that is the primary key, but given no value and not declared auto_increment.

EDIT:

I suspect you want the following code:

CREATE TABLE suppliers (
    supplierId int NOT NULL auto_increment primary key,
    supp_name varchar(255) unique,
    company_name varchar(15) NOT NULL,
    town varchar(15),
    phone varchar(15)
);

INSERT INTO Suppliers(supp_name, company_name, town, phone)
    Values ('ADT217', 'AdTec', 'Birmingham', '0121-368-1597'),
           ('CPS533', 'CPS', 'Maidenhead', '01382-893715'),
           ('FCL162', 'ForComp Ltd', 'Nottingham', '01489-133722'),
           ('KBC355', 'KBC Computers', 'Glasgow', '0141-321-1497');

Some notes:

  • Usually you want varchar() rather than char(), unless you really like lots of spaces at the end of strings.
  • I added a unique supplier name to the table and declared the id to be a auto_increment.
  • Single quotes are ANSI standard for string constants. MySQL (and some other databases) allow double quotes, but there is no reason to not use the standard.

Upvotes: 4

Related Questions