Ошибка field id doesn t have a default value

I am new to this SQL; I have seen similar question with much bigger programs, which I can’t understand at the moment. I am making a database for games of cards to use in my homepage.

I am using MySQL Workbench on Windows. The error I get is:

Error Code: 1364. Field ‘id’ doesn’t have a default value

CREATE TABLE card_games
(
nafnleiks varchar(50), 
leiklysing varchar(3000), 
prentadi varchar(1500), 
notkunarheimildir varchar(1000), 
upplysingar varchar(1000), 
ymislegt varchar(500), 
id int(11) PK
);

insert into card_games (nafnleiks, leiklysing, prentadi, notkunarheimildir, upplysingar, ymislegt)

values('Svartipétur',
'Leiklýsingu vantar',
'Er prentað í: Þórarinn Guðmundsson (2010). Spilabókin - Allir helstu spilaleikir og spil.',
'Heimildir um notkun: Árni Sigurðsson (1951). Hátíðir og skemmtanir fyrir hundrað árum',
'Aðrar upplýsingar',
'ekkert hér sem stendur'
);

values('Handkurra',
'Leiklýsingu vantar',
'Er prentað í: Þórarinn Guðmundsson (2010). Spilabókin - Allir helstu spilaleikir og spil.',
'Heimildir um notkun', 
'Aðrar upplýsingar',
'ekkert her sem stendur'
);

values('Veiðimaður',
'Leiklýsingu vantar',
'Þórarinn Guðmundsson (2010). Spilabókin - Allir helstu spilaleikir og spil. Reykjavík: Bókafélagið. Bls. 19-20.',
'vantar',
'vantar',
'vantar'
);

As id is the primary key, you cannot have different rows with the same value. Try to change your table so that the id is auto incremented:

id int NOT NULL AUTO_INCREMENT

and then set the primary key as follows:

PRIMARY KEY (id)

All together:

CREATE TABLE card_games (
   id int(11) NOT NULL AUTO_INCREMENT,
   nafnleiks varchar(50),
   leiklysing varchar(3000), 
   prentadi varchar(1500), 
   notkunarheimildir varchar(1000),
   upplysingar varchar(1000),
   ymislegt varchar(500),
   PRIMARY KEY (id));

Otherwise, you can indicate the id in every insertion, taking care to set a different value every time:

insert into card_games (id, nafnleiks, leiklysing, prentadi, notkunarheimildir, upplysingar, ymislegt)

values(1, 'Svartipétur', 'Leiklýsingu vantar', 'Er prentað í: Þórarinn Guðmundsson (2010). Spilabókin - Allir helstu spilaleikir og spil.', 'Heimildir um notkun: Árni Sigurðsson (1951). Hátíðir og skemmtanir fyrir hundrað árum', 'Aðrar upplýsingar', 'ekkert hér sem stendur' );

Since mysql 5.6, there is a new default that makes sure you are explicitly inserting every field that doesn’t have a default value set in the table definition.

to disable and test this: see this answer here: mysql error 1364 Field doesn’t have a default values

I would recommend you test without it, then reenable it and make sure all your tables have default values for fields you are not explicitly passing in every INSERT query.

If a third party mysql viewer is giving this error, you are probably limited to the fix in that link.

This is caused by MySQL having a strict mode set which won’t allow INSERT or UPDATE commands with empty fields where the schema doesn’t have a default value set.

There are a couple of fixes for this.

First ‘fix’ is to assign a default value to your schema. This can be done with a simple ALTER command:

ALTER TABLE `details` CHANGE COLUMN `delivery_address_id` `delivery_address_id` INT(11) NOT NULL DEFAULT 0 ;

However, this may need doing for many tables in your database schema which will become tedious very quickly. The second fix is to remove sql_mode STRICT_TRANS_TABLES on the mysql server.

If you are using a brew installed MySQL you should edit the my.cnf file in the MySQL directory. Change the sql_mode at the bottom:

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode=NO_ENGINE_SUBSTITUTION

Save the file and restart Mysql.

Source: https://www.euperia.com/development/mysql-fix-field-doesnt-default-value/1509

answered Dec 15, 2015 at 16:00

make sure that you do not have defined setter for the for your primary key in model class.

public class User{
@Id
@GeneratedValues
private int user_Id;
private String userName;

public int getUser_Id{
return user_Id;
}

public String getUserName{
return userName;
}

public void setUserName{
this.userName=userName;
}
}

answered Mar 12, 2015 at 9:59

To detect run:

select @@sql_mode
-- It will give something like:
-- STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

To fix, run:

set global sql_mode = ''

answered Oct 22, 2020 at 18:17

if you add the AUTO_INCREMENT clause to the primary key id field in the database table as shown below it will work.

CREATE TABLE user_role (
user_role_id bigint NOT NULL AUTO_INCREMENT,
user_id bigint NOT NULL,
role_id bigint NOT NULL,

answered Apr 5, 2021 at 0:49

Disable FOREIGN_KEY_CHECKS and then

`SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE card_games MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;

SET FOREIGN_KEY_CHECKS = 1;`

answered May 20, 2021 at 17:10

For me the issue got fixed when I changed

<id name="personID" column="person_id">
    <generator class="native"/>
</id>

to

<id name="personID" column="person_id">
    <generator class="increment"/>
</id>

in my Person.hbm.xml.

after that I re-encountered that same error for an another field(mobno). I tried restarting my IDE, recreating the database with previous back issue got eventually fixed when I re-create my tables using (without ENGINE=InnoDB DEFAULT CHARSET=latin1; and removing underscores in the field name)

CREATE TABLE `tbl_customers` (
  `pid` bigint(20) NOT NULL,
  `title` varchar(4) NOT NULL,
  `dob` varchar(10) NOT NULL,
  `address` varchar(100) NOT NULL,
  `country` varchar(4) DEFAULT NULL,
  `hometp` int(12) NOT NULL,
  `worktp` int(12) NOT NULL,
  `mobno` varchar(12) NOT NULL,
  `btcfrom` varchar(8) NOT NULL,
  `btcto` varchar(8) NOT NULL,
  `mmname` varchar(20) NOT NULL
)

instead of

CREATE TABLE `tbl_person` (
  `person_id` bigint(20) NOT NULL,
  `person_nic` int(10) NOT NULL,
  `first_name` varchar(20) NOT NULL,
  `sur_name` varchar(20) NOT NULL,
  `person_email` varchar(20) NOT NULL,
  `person_password` varchar(512) NOT NULL,
  `mobno` varchar(10) NOT NULL DEFAULT '1',
  `role` varchar(10) NOT NULL,
  `verified` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I probably think this due to using ENGINE=InnoDB DEFAULT CHARSET=latin1; , because I once got the error org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Unknown column 'mob_no' in 'field list' even though it was my previous column name, which even do not exist in my current table. Even after backing up the database(with modified column name, using InnoDB engine) I still got that same error with old field name. This probably due to caching in that Engine.

answered Jun 24, 2017 at 16:15

I landed this question in 2019. MY problem was updating table1 with table2 ignoring the variables with different name in both tables. I was getting the same error as mentioned in question: Error Code: 1364. Field ‘id’ doesn’t have a default value in mysql. Here is how solved it:

Table 1 Schema : id ( unique & auto increment)| name | profile | Age
Table 2 Schema: motherage| father| name| profile

This solved my error:
INSERT IGNORE INTO table2 (name,profile) SELECT name, profile FROM table1

answered Sep 7, 2019 at 20:12

I had an issue on AWS with mariadb — This is how I solved the STRICT_TRANS_TABLES issue

SSH into server and chanege to the ect directory

[ec2-user]$ cd /etc

Make a back up of my.cnf

[ec2-user etc]$ sudo cp -a my.cnf{,.strict.bak}

I use nano to edit but there are others

[ec2-user etc]$ sudo nano my.cnf

Add this line in the the my.cnf file

#
#This removes STRICT_TRANS_TABLES
#
sql_mode=""

Then exit and save

OR if sql_mode is there something like this:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Change to

sql_mode=""

exit and save

then restart the database

[ec2-user etc]$ sudo systemctl restart mariadb

answered Nov 17, 2018 at 9:45

It amazing for me, for solving Field ‘id’ doesn’t have a default value? I have tried all the possible ways what are given here like..

set sql_mode=""
set @@sql_mode = ''; etc

but unfortunately these didn’t work for me.
So after long investigation, I found that

@Entity
@Table(name="vendor_table")
public class Customer {
    @Id
    @Column(name="cid")
    private int cid;
.....
}

@Entity
@Table(name="vendor_table")
public class Vendor {
    @Id
    private int vid;
    @Column
    private String vname;
    .....
}

here you can see that both tables are having same name. It is very funny mistake, was done by me :)))). After correcting this,my problem was gone off.

answered Nov 22, 2018 at 13:50

If you are creating your schema using MySQL Workbench, you can check this checkbox.

If you can’t see the image, it’s the column titled AI, if you hover over it you’ll see it’s label Mark column as AUTO_INCREMENT.

answered May 12, 2022 at 13:16

add a default value for your Id lets say in your table definition this will solve your problem.