2

I have a problem with mysql table while try to insert values in database.

I followed this tutorial

http://sqllessons.com/categories.html

and created table like the table from tutorial

table code 

create table categories
( id       integer     not null  primary key 
, name     varchar(37) not null
, parentid integer     null
, foreign key parentid_fk (parentid) 
      references categories (id)
);

error SQL query: Edit Edit

INSERT INTO `mydb`.`categories` (
`id` ,
`name` ,
`parentid`
)
VALUES (
'', 'groceries', NULL
), (
'', 'snacks', NULL
)

MySQL said: Documentation
#1062 - Duplicate entry '0' for key 'PRIMARY'

Help me to solve this.

djuro djuric
  • 39
  • 1
  • 1
  • 6

4 Answers4

7

Declare the value to be auto incrementing and don't insert it. So:

create table categories (
    id       integer     not null  auto_increment primary key,
    name     varchar(37) not null,
    parentid integer     null,
    foreign key parentid_fk (parentid) references categories (id)
);

And then:

INSERT INTO `mydb`.`categories` (`name`, `parentid`)
    VALUES ('groceries', NULL),
           ('snacks', NULL);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I've commented everyone else's answer so I feel a little bad to not comment yours as well: This answer hits the nail on the head `:-)` – Martin Jun 22 '17 at 11:55
0

You want to insert an empty value (0) twice to field, that you said is PRIMARY KEY. Primary key from definition has no duplicate.

Gadziu
  • 657
  • 7
  • 21
0

You need to specify the primary key as AUTO_INCREMENT and no need to insert value for 'id' in the query

Riyaz
  • 139
  • 11
0

Every Primary Key needs to be unique. You inserted 2 rows with the Primary key '0'. Instead of ' ' you should insert an ID.

Edit: Sry my bad, ID not Index.

Frivolin
  • 41
  • 5