[mysql] 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'
);

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.

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

Solution: Remove STRICT_TRANS_TABLES from sql_mode

To check your default setting,

mysql> set @@sql_mode = 
'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

Run a sample query

mysql> INSERT INTO nb (id) VALUES(3);
ERROR 1364 (HY000): Field 'field' doesn't have a default value

Remove your STRICT_TRANS_TABLES by resetting it to null.

mysql> set @@sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

Now, run the same test query.

mysql> INSERT INTO nb (id) VALUES(3);
Query OK, 1 row affected, 1 warning (0.00 sec)

Source: https://netbeans.org/bugzilla/show_bug.cgi?id=190731

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.

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 = ''

There are 2 solutions mentioned below:

Solution 1

MySQL is most likely in STRICT SQL mode. Try to execute SQL query SET GLOBAL sql_mode='' or edit your my.cnf / my.ini to make sure you aren't setting STRICT_ALL_TABLES and/or STRICT_TRANS_TABLES.

Solution 2

If Solution-1 is not working then try Solution-2 as given in below steps:

1. Run MySQL Administrator tool as Administrator.
2. Then go to Startup Variable.
3. Then go to the Advance tab.
4. find SQL Mode and remove the STRICT_ALL_TABLES and/or STRICT_TRANS_TABLES and then Click on Apply Changes.
5. Restart MySQL Server.
6. Done.

Note: I have tested these solutions in MySQL Server 5.7

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' );

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

I was getting error while ExecuteNonQuery() resolved with adding AutoIncrement to Primary Key of my table. In your case if you don't want to add primary key then we must need to assign value to primary key.

ALTER TABLE `t1` 
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT ;

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.

As a developer it is highly recommended to use STRICT mode because it will allow you to see issues/errors/warnings that may come about instead of just working around it by turning off strict mode. It's also better practice.

Strict mode is a great tool to see messy, sloppy code.

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;
}
}

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

The id should set as auto-increment.

To modify an existing id column to auto-increment, just add this

ALTER TABLE card_games MODIFY id int NOT NULL AUTO_INCREMENT;

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