Slick 3.0.0 AutoIncrement Composite Key

Question

I have a table structure as below:

Table1:
  id: Int
  name: String
  version: Int

The corresponding Slick representation of the table would be:

  class Table1(tag: Tag) extends Table[(Int, String, Int)](tag, "TABLE1") {
    def id = column[Int]("ID")
    def name = column[String]("NAME")
    def version = column[Int]("VERSION")

    def pk = primaryKey("pk_a", (id, version))
  }

How can I make the version to auto increment for that corresponding id?

I can have elements like:

id name version
1  n1   1
1  n2   2
2  xyz  1
3  bmp  1
3  abc  2

So the above structure has id's 1 and 3 in versions 1 and 2, I want to have the version auto increment. If there is a feature in-built, I would like to use it. If not, I have to first issue a select, add 1 t the version and create a new record. Any suggestions?

If you use the select first version, at some point, you will have duplicate key problems. If two queries run at the same time. — i.am.michiel, May 24 '15 at 07:40
So what could be the best way to do it? I want to refrain from using triggers! — joesan, May 24 '15 at 07:52
You can't. You will have to use triggers if you want to garantuee there will be no duplicate keys. — i.am.michiel, May 24 '15 at 14:21

i.am.michiel · Accepted Answer · 2015-05-24T14:21:48.110

1

You will have to use triggers. That way on insert, you can ask MySQL or Postgresql to set the value of version to the result of a SQL query :

select max(version) from Table1 where id = x

edited May 24 '15 at 14:21

answered May 24 '15 at 07:37

i.am.michiel

10,281
7
50
86

I'm using Postgres and since I'm new to Postgres, I have to look up the documentation on how I could set up a trigger! – joesan May 24 '15 at 08:29

Slick 3.0.0 AutoIncrement Composite Key

1 Answers1

Linked