CODEWITHSUNDEEP
javamysqlsql
Nicster15
Nicster15

Reputation: 169

SQL How to make the first column increment

I'm attempting to make my first column in a table just automatically increment whenever a new row is added, however instead "NULL" is inserted there.

Here is my code for inserting a new row:

String update = "INSERT INTO help(name, area, date, message) VALUES(?, ?, ?, ?)";
    try {
        connection = plugin.getHikari().getConnection();
        // Inserting into the table
        statement = connection.prepareStatement(update);
        // Replace the '?' with the actual information
        statement.setString(1, name);
        statement.setString(2, area);
        statement.setString(3, date);
        statement.setString(4, message);
        statement.execute();
    } catch (SQLException e) {
        e.printStackTrace();
    }

Here is how I create my table:

 statement = connection.prepareStatement(
                "CREATE TABLE IF NOT EXISTS help" +
                        "(" +
                        "id int," +
                        "name varchar(20)," +
                        "area varchar(25)," +
                        "date varchar(15)," +
                        "message varchar(255)" +
                        ")");
        statement.execute();

Here is a statement I run everytime the application is loaded / starts up:

statement = connection.prepareStatement("ALTER TABLE help MODIFY COLUMN id INT auto_increment");

Thanks, - Nicster

Upvotes: 0

Views: 155

Answers (2)

Chris
Chris

Reputation: 678

You can do everything you need to in the create statement. The problem is your id column is nullable and is not a primary key.

Just change your create statement to

statement = connection.prepareStatement(
            "CREATE TABLE IF NOT EXISTS help" +
                    "(" +
                    "id int not null auto_increment," +
                    "name varchar(20)," +
                    "area varchar(25)," +
                    "date varchar(15)," +
                    "message varchar(255)" +
                    "PRIMARY KEY (id)" +
                    ")");
    statement.execute();

You should not need the alter table statement. Hope that helps

Upvotes: 2

KP.
KP.

Reputation: 393

Either you add a primary key to the table "PRIMARY KEY (id))" which would auto_increment itself, or you have have to use java logic to increment the value each time you insert the row

Upvotes: 0

Related Questions