CODEWITHSUNDEEP
mysqldatabase
Ruslan Kryzhanovskiy
Ruslan Kryzhanovskiy

Reputation: 89

[23000][1062] Duplicate entry '0' for key 'PRIMARY'

when I filled out the database, I received a problem in the form of duplication of the primary key entry, but the fact is that the field "status" field in my database is not the primary key or its part.

DB Script

CREATE TABLE IF NOT EXISTS travel.`users` (
  `login` VARCHAR(25) NOT NULL,
  `password` VARCHAR(25) NOT NULL,
  `email` VARCHAR(40) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `surname` VARCHAR(20) NOT NULL,
  `telephone` VARCHAR(20) NOT NULL,
  `role` VARCHAR(30) NOT NULL,
  `status` TINYINT(1) NOT NULL,
  `image` VARCHAR(250) NOT NULL,
  PRIMARY KEY (`login`),
  UNIQUE INDEX `login_UNIQUE` (`login` ASC))
  ENGINE = InnoDB;

SQL Query

INSERT INTO users VALUES
  (login = 'admin', password = 'admin', email = '[email protected]', name = 'Иван',
   surname = 'Иванов', telephone = '+380674002092', role = 'ADMINISTRATOR', status = '0',
   image = 'D:/server/admin.img');

INSERT INTO users VALUES
  (login = 'manager', password = 'manager', email = '[email protected]', name = 'Петров',
   surname = 'Пётр', telephone = '+380964002092', role = 'MANAGER', status = '0',
   image = 'D:/server/manager.img');

During execution of the second INSERT I receive the following error [23000][1062] Duplicate entry '0' for key 'PRIMARY'

Upvotes: 1

Views: 3995

Answers (3)

Bira
Bira

Reputation: 5506

`user_id` int(8) NOT NULL AUTO_INCREMENT

you must have a auto increment field.

Upvotes: 4

miiiii
miiiii

Reputation: 1610

Raja's answer is correct, but I want to add few more information for others

His answer..

Remove the Column name .Do not need to specify the column.

INSERT INTO users VALUES ( 'admin', 'admin', '[email protected]', 'Иван', 'Иванов', '+380674002092', 'ADMINISTRATOR', '0', 'D:/server/admin.img');

INSERT INTO users VALUES ( 'manager', 'manager', '[email protected]', 'Петров', 'Пётр', '+380964002092', 'MANAGER', '0', 'D:/server/manager.img');

My additions...

We do not need to specify the columns if we are doing full insert, if we want to insert data to only some rows, we specify the only columns to that we are inserting in.

Here is a sample, 

INSERT INTO users(login, password, status) VALUES
  ( 'manager1',  'manager1',  '0');

This will insert values only in login, password and status column.

Also, One suggestion, avoid using varchar datatype as Primary Key, as it would affect the performance for indexing in main memory. Instead use int or long (bigint in most of RDBMS).

And, you can set that to on Auto Increment and it makes easy to use them as Foreign Key.

Here is what you need to change if you want to make it AutoIncrement..

    CREATE TABLE IF NOT EXISTS travel.`users` (
        `id` int(8) NOT NULL AUTO_INCREMENT, 
        `login` VARCHAR(25) NOT NULL,
         ....
        PRIMARY KEY (`id`),

Upvotes: 0

Raja Ramachandran
Raja Ramachandran

Reputation: 456

Remove the Column name .Do not need to specify the column.

INSERT INTO users VALUES
  ( 'admin',  'admin', '[email protected]',  'Иван',
    'Иванов',  '+380674002092',  'ADMINISTRATOR',  '0',
    'D:/server/admin.img');

INSERT INTO users VALUES
  ( 'manager',  'manager',  '[email protected]',  'Петров',
    'Пётр', '+380964002092',  'MANAGER',  '0',
    'D:/server/manager.img');

Upvotes: 0

Related Questions