SQLException java.sql.SQLException: Field 'userid' doesn't have a default value

Question

My user table has previously string primary key now i added a new field userid bigint(20) and made this primary key. then added

ALTER TABLE smsusers AUTO_INCREMENT = 2335;

Total records in user table are 2334 next record should take value 2335 and should be auto incremented as records are added.

Problem is while inserting data from my application it is showing following error

SQLException  java.sql.SQLException: Field 'userid' doesn't have a default value

How to resolve this problem

EDIT: while trying to insert new data

    INSERT INTO `dbname`.`smsusers` ( `password`, `fname`, `lname`,
 `mailid`, `dob`, `gender`) VALUES ('asdf', 'asdf', 'asdf',
 '[email protected]', '2013-10-10', 'm');

It is showing following error

#1062 - Duplicate entry '0' for key 1

EDIT: Initially my primary key was id than i added userid and made this primary key

MY table structure is

CREATE TABLE `smsusers` (
 `id` varchar(60) NOT NULL DEFAULT '',
 `password` varchar(50) NOT NULL,
 `fname` varchar(30) NOT NULL,
 `lname` varchar(30) DEFAULT NULL,
 `mailid` varchar(50) NOT NULL,
 `dob` date NOT NULL,
 `gender` varchar(10) NOT NULL,
 `city` varchar(70) DEFAULT NULL,
 `userid` int(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (`userid`),
 KEY `id_pk_unique` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

While inserting data in smsusers it it only taking default value at 0 not the incremented value.

Praveen Prasannan · Accepted Answer · 2013-11-04 10:33:31Z

5

Run below queries. Then no worries. :)

   ALTER TABLE smsusers MODIFY COLUMN userid INT(20) AUTO_INCREMENT;

   ALTER TABLE smsusers AUTO_INCREMENT = 2335;

answered Nov 4, 2013 at 10:33

Praveen Prasannan

7,15310 gold badges53 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

manish Sahu Over a year ago

This problem was from last 2 months. New users were not able to register on my site. After analyzing I find above problem. Thanks for resolving my problem.

Andrey Deineko Over a year ago

I have no idea what this did, but it solved my same issue

Vivek Pal · Accepted Answer · 2015-03-12 09:52:22Z

make sure that you are not using setter for your primary key in model class.

@Entity
public class BlogDetails {
@Id
@GeneratedValue
@Column(name="B_Id",nullable=false)
private int b_Id;
@Column(name="EmailId")
private String emailId;
@Column(name="Title")
private String title;
@Column(name="BriefDetail")
private String briefDetail;
@Column(name="Detail")
private String detail;

public BlogDetails() {
}

public BlogDetails(String EmailId, String Title, String BriefDetail, String      

Detail) {
this.emailId=EmailId;
this.title=Title;
this.briefDetail=BriefDetail;
this.detail=Detail;
}

public int getB_Id() {
return b_Id;
}

public String getEmailId() {
return emailId;
}

public void setEmailId(String emailId) {
this.emailId = emailId;
}

public String getTitle() {
return title;
}

public void setTitle(String title) {
this.title = title;
}

public String getBriefDetail() {
return briefDetail;
}

public void setBriefDetail(String briefDetail) {
this.briefDetail = briefDetail;
}

public String getDetail() {
return detail;
}

public void setDetail(String detail) {
this.detail = detail;
}


}

Jakob · Accepted Answer · 2013-11-04 09:52:14Z

0

Since your table has userid as PRIMARY KEY you cannot use DEFAULT 0 as a value. PRIMARY KEY must be unique and it wont be if you have a default value on it. Change DEFAULT 0 to AUTO_INCREMENT instead.

It never uses the auto incremented value because you never say which column should be auto_increment.

answered Nov 4, 2013 at 9:52

Jakob

7384 silver badges17 bronze badges

Collectives™ on Stack Overflow

SQLException java.sql.SQLException: Field 'userid' doesn't have a default value

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related