Field 'id' doesn't have a default value?

Question

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

maybe you want to mark that column `id` with an `auto-increment` status? — Yohanes Khosiawan 许先汉, Sep 16 '14 at 09:26
change `id int(11) PK` to `id int(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)` — Abhik Chakraborty, Sep 16 '14 at 09:27

score 67 · Answer 1 · edited Oct 30 '14 at 10:20

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

score 49 · Answer 2 · edited Jul 19 '17 at 21:55

49

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:

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

Note: I have tested these solutions in MySQL Server 5.7

edited Jul 19 '17 at 21:55

Don't Panic

41,125
10
61
80

answered Apr 29 '16 at 10:45

Renish Aghera

705
5
8

1

Just wanted to add that one can see the modes currently set by running `SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;` – pazof Oct 02 '19 at 14:36
8

Removing strict mode - is not a solution. It is a good way to shoot yourself in the foot in the future. This mode prevents error, it is like a protection from bad practices and wrong solutions, which you just remove. – Alexandr Nov 07 '19 at 10:21
You can just use `SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'STRICT_TRANS_TABLES', '');` to unset just the setting. – Dean Or Jan 23 '20 at 17:05

John Joe · Answer 3 · 2018-07-17T04:19:59.710

46

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;

edited Jul 17 '18 at 04:19

answered Mar 22 '17 at 02:46

John Joe

12,412
16
70
135

1

Worked for me. Thank you sir! – Evan Gertis Mar 18 '19 at 17:14
1

Thank you that was exactly my problem ! – Lucas Ramos Mar 18 '22 at 15:32

score 11 · Answer 4 · answered Apr 09 '19 at 12:16

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 ;

score 5 · Answer 5 · edited May 23 '17 at 10:31

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.

score 5 · Answer 6 · answered Dec 15 '15 at 16:00

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

score 4 · Answer 7 · answered Mar 12 '15 at 09:59

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

score 4 · Answer 8 · answered Oct 22 '20 at 18:17

4

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 '20 at 18:17

Shadi Alnamrouti

11,796
4
56
54

score 3 · Answer 9 · answered Apr 05 '21 at 00:49

3

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 05 '21 at 00:49

user1419261

829
8
5

score 2 · Answer 10 · answered Jun 30 '16 at 12:16

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

To check the default setting: `select @@sql_mode` – Shadi Alnamrouti Oct 22 '20 at 18:11 — Shadi Alnamrouti, Oct 22 '20 at 18:11

score 2 · Answer 11 · answered May 20 '21 at 17:10

2

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 '21 at 17:10

Ahmad Alhaj-Karim

184
8

user158 · Answer 12 · 2017-06-25T04:00:03.910

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.

score 1 · Answer 13 · answered Sep 07 '19 at 20:12

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:

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

score 0 · Answer 14 · answered Dec 06 '16 at 05:28

0

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.

answered Dec 06 '16 at 05:28

ex8

97
1
7

4

this is not an answer, but could be a comment on other answers suggesting to remove STRICT mode – Arsalan Ahmad Sep 20 '17 at 06:40

score 0 · Answer 15 · answered Nov 17 '18 at 09:45

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

score 0 · Answer 16 · answered Nov 22 '18 at 13:50

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.

score 0 · Answer 17 · answered May 12 '22 at 13:16

0

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 '22 at 13:16

Mahad Ahmed

179
4
7

score -7 · Answer 18 · answered May 29 '19 at 17:53

-7

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

answered May 29 '19 at 17:53

Flavins

123
1
5

It could have been better if you have given a query snippet with explanations – Prasanth Rajendran Nov 13 '19 at 18:50

Field 'id' doesn't have a default value?

18 Answers18

Solution 1

Solution 2

Linked

Related