EMPTY TABLE Duplicate entry '1' for key 'PRIMARY'

Question

I have a strange problem with my MariaDB database. I create an empty table with the following code:

drop table if exists Subject;
CREATE TABLE Subject (
  id integer primary key auto_increment,
  code varchar(100) unique not null,
  name text not null
);

Query executed OK, 0 rows affected.

I try to insert some data into the table:

INSERT INTO Subject (id, code, name) VALUES
(0,'KMI/AIdb/PHW/15','Počítačový hardvér'),
(1,'KMI/AIdb/DBA/15','Tvorba databázových aplikácií'),
(2,'KMI/SPRVdb/INF/16','Informatika a základy správy databáz'),
(3,'KMI/AIdb/PR4/15','Programovanie 4 - Objektové programovanie'),
(4,'KMI/AIdb/DBS/15','Databázové informačné systémy');

Error in query (1062): Duplicate entry '1' for key 'PRIMARY'

If I run the same query one more time:

INSERT INTO Subject (id, code, name) VALUES
(0,'KMI/AIdb/PHW/15','Počítačový hardvér'),
(1,'KMI/AIdb/DBA/15','Tvorba databázových aplikácií'),
(2,'KMI/SPRVdb/INF/16','Informatika a základy správy databáz'),
(3,'KMI/AIdb/PR4/15','Programovanie 4 - Objektové programovanie'),
(4,'KMI/AIdb/DBS/15','Databázové informačné systémy');

Query executed OK, 5 rows affected.

I believe it has something to do with the auto_increment, but I have a huge database dump that I would like to insert. Is this a bug, or is this an expected behavior?

Answer 1

AUTO_INCREMENT attribute can be used to generate a unique identity for new rows.

You can also explicitly assign 0 to the column to generate sequence numbers unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled.

Read here for more details