Inserting 0 for an auto-incrementing column in mysql

Question

I am using slick 3.2.1

I need to insert "0" into a column which is auto-increment (MySQL).

I wrote this code

val foo = Foo(0, "FOO")
val fooQuery = Tables.FooTable forceInsert  foo
Await.result(db.run(fooQuery), Duration.Inf)

My hope was that forceinsert will put the exact value of 0 in the column rather than using the database provided incremented value.

The code above executes fine. but when I go into DB I see that the ID is 1. So its not forcing the id to 0. it is still using the database provided value.

score 0 · Accepted Answer · answered Aug 28 '17 at 15:52

0

You will not be able to insert into any auto-incremented column if your value is lower than an existing one. You can do this is if the table in empty I believe.

answered Aug 28 '17 at 15:52

jonathan.ihm

108
1
1
8

I assumed that there is existing data. – jonathan.ihm Aug 28 '17 at 15:52
I truncated the table before loading the data. – Knows Not Much Aug 28 '17 at 15:55
1

Any chance that this answer would help: https://stackoverflow.com/questions/1142472/how-to-force-mysql-to-take-0-as-a-valid-auto-increment-value – jonathan.ihm Aug 28 '17 at 15:59
Yes that works when I try from mysql command line. Any idea how can I do the same using Slick? – Knows Not Much Aug 28 '17 at 16:06
The only thing I can think of is to try using plain SQL queries – jonathan.ihm Aug 28 '17 at 16:13

score 0 · Answer 2 · edited Nov 03 '21 at 20:38

This code is working.

For auto-increment add zero to corresponding parameter value in scala and create table with auto increment field[id]

**/*Table creation*/**

CREATE TABLE `recommendation` 
(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `product_id` int(11) DEFAULT NULL,
    `rec_product_id` int(11) DEFAULT NULL,
    `color_id` int(11) DEFAULT '1',
    `uiposition` int(11) DEFAULT '0',

    PRIMARY KEY (`id`)
)

/* scala Code */

val insertSql = """
                      |INSERT INTO `wellness`.`recommendation` (`id`, `product_id`, `rec_product_id`, `color_id`, `uiposition`)
                      |VALUES (?,?,?,?,?)
                    """.stripMargin


    val preparedStmt: sql.PreparedStatement = conn.prepareStatement(insertSql)

    preparedStmt.setInt (1, 0)//Auto increment in scala**
    preparedStmt.setInt (2, vproid)
    preparedStmt.setInt (3, recoid.toInt)
    preparedStmt.setInt    (4, 1)
    preparedStmt.setInt    (5, uipos)

Inserting 0 for an auto-incrementing column in mysql

2 Answers2