0

I was trying to create an insert query in Java to MySQL server but the thing is it keep asking for value for column "user id" even though I've already define it as AUTO_INCREMENT. I've tried to set the value to NULL too but it said "column user id cannot be null".

From what I know in SQL you don't have to define value for auto increment type right ?

Query:

String query = "INSERT INTO 
user(userId, username, password, gender, country, role) 
VALUES(NULL,'"+uu+"', '"+pp+"', '"+gg+"','"+cc+"', '"+rr+"')";

Errors :

java.sql.SQLIntegrityConstraintViolationException: Column 'userId' cannot be null

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1333) at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2106) at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1243) at main.Connect.updateData(Connect.java:43) at main.Regis.actionPerformed(Regis.java:189) at javax.swing.AbstractButton.fireActionPerformed(Unknown Source) at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source) at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source) at javax.swing.DefaultButtonModel.setPressed(Unknown Source) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source) at java.awt.Component.processMouseEvent(Unknown Source) at javax.swing.JComponent.processMouseEvent(Unknown Source) at java.awt.Component.processEvent(Unknown Source) at java.awt.Container.processEvent(Unknown Source) at java.awt.Component.dispatchEventImpl(Unknown Source) at java.awt.Container.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source) at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source) at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source) at java.awt.Container.dispatchEventImpl(Unknown Source) at java.awt.Window.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.EventQueue.dispatchEventImpl(Unknown Source) at java.awt.EventQueue.access$500(Unknown Source) at java.awt.EventQueue$3.run(Unknown Source) at java.awt.EventQueue$3.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source) at java.awt.EventQueue$4.run(Unknown Source) at java.awt.EventQueue$4.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source) at java.awt.EventQueue.dispatchEvent(Unknown Source) at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.run(Unknown Source)

Table definition :

|Column  |Type         |Null|key|Default|Extra|
+--------+-------------+----+---+-------+-----+
|userId  |int          |NO  |___|NULL   |     |
|username|varchar(255) |NO  |___|NULL   |     |
|password|varchar(255) |NO  |___|NULL   |     |
|gender  |varchar(255) |NO  |___|NULL   |     |
|country |varchar(255) |NO  |___|NULL   |     |
|role    |varchar(255) |NO  |___|NULL   |     |

Note : from the .sql file that I get there is alter table code for table user

ALTER TABLE `user`
MODIFY `userId` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
luucid
  • 1
  • 2
  • 2
    [Please do not upload images of code/errors when asking a question.](//meta.stackoverflow.com/q/285551) – shmosel Dec 31 '21 at 22:04
  • 1
    can you share tha table definition ? Do `desc user;` and show us by [edit]ing your post – azro Dec 31 '21 at 22:05
  • i really dont know how this stack overflow thing works im new, so im sorry if i break some rules – luucid Dec 31 '21 at 22:24
  • 3
    Just leave userId out of the insert statement. – Bohemian Dec 31 '21 at 22:25
  • yea i already did that but it said "Field 'userId' doesn't have a default value" – luucid Dec 31 '21 at 22:27
  • it sounds like maybe your alter table didn't succeed? could you edit your question and show (as text, not an image) the output of `show create table users;`? @azro that provides so much more helpful information than desc – ysth Dec 31 '21 at 22:51
  • I mean `show create table user;` – ysth Jan 01 '22 at 00:22
  • 1
    Also, don't make SQL statements by concatenating strings like that - you are wide open to SQL Injection attacks. Use PreparedStatement with setValue. – greg-449 Jan 01 '22 at 13:05

2 Answers2

1

You can't make a column AUTO_INCREMENT unless it's has a key (that is, index) on it. Best if it's a PRIMARY KEY or UNIQUE KEY.

But I see in your example of describe table, the column is not a key:

|Field   |Type         |Null|key|Default|Extra|
|--------|-------------|----|---|-------|-----|
|userId  |int          |NO  |___|NULL   |     |

                             ^^^ ideally this should say "PRI"

So your ALTER TABLE to make the column AUTO_INCREMENT probably failed. You can confirm this:

SHOW CREATE TABLE `user`\G

Do you see the AUTO_INCREMENT option next to the userId column? I don't think you will.

You can try again to make the column a primary key and make it AUTO_INCREMENT:

ALTER TABLE `user`
 ADD PRIMARY KEY (`userId`),
 MODIFY `userId` INT AUTO_INCREMENT;

Don't bother with INT(255). The length argument for an integer is a common source of confusion. It has almost no purpose or effect, and it's deprecated in MySQL 8.0.

Don't bother making the column NOT NULL. That will happen automatically as you add the PRIMARY KEY constraint.

Don't bother setting the AUTO_INCREMENT=8. The next AI value will automatically be set to the highest value in that column, plus one. It can never be less than the greatest value in that column.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

Try this code, must work if you already defined userId is an auto_increament.

String query = "INSERT INTO user(username,password,gender,country,role)  VALUES('"+uu+"', '"+pp+"', '"+gg+"','"+cc+"', '"+rr+"')";
azro
  • 53,056
  • 7
  • 34
  • 70
Ahmed Mera
  • 15
  • 4