How to define an additional foreign key in Slick?

Question

I have a table like this:

object Addresses extends Table[AddressRow]("address") { def id = column[Int]("id", O.PrimaryKey, O.AutoInc) def street = column[String]("street") def number = column[String]("number") def zipcode = column[String]("zipcode") def city = column[String]("city") def country = column[String]("country") def geoLocationId = column[Int]("geo_location_id", O.Nullable) // Foreign keys. def geoLocation = foreignKey("fk_geo_location", geoLocationId, GeoLocations)(_.id) // Rest of my code. ... } 

where is my case class:

 case class AddressRow( id: Option[Int] = None, street: String, number: String, zipcode: String, city: String, country: String, geoLocationId: Option[Int]) 

As you can see, geoLocation is an optional foreign key ....

I cannot find a way to describe this “Optional” in my foreign key definition.

I tried:

  def geoLocation = foreignKey("fk_geo_location", geoLocationId.asColumnOf[Option[Int]], GeoLocations)(_.id) 

but I get:

Called: scala.slick.SlickException: you cannot use the Apply column Cast function in a foreign key constraint (only columns with names are allowed)

Does anyone have a suggestion?

Answer 1

I do not think that what you are trying to do can be achieved using foreign keys. Check joining and custom types from Slick docs.

Check out the example with leftJoin :

 val explicitLeftOuterJoin = for { (c, s) <- Coffees leftJoin Suppliers on (_.supID === _.id) } yield (c.name, s.name.?) 

So, if you want to request all your Addresses , you should start with something like

 val addressGeolocQuery = for { (addr, loc) <- Addresses leftJoin GeoLocations on (_.geoLocationId === _.id) } yield addr.id ~ loc.prop1.? ~ loc.prop2.? /*and so on*/ 

You can then match the results of this query so that you return the actual instance of Address , complete with Option[GeoLocation] . That's why I linked the “user types” in the docs ... which is a new feature for me (I was familiar with ScalaQuery, which was the previous incarnation of Slick), but it looks pretty promising.

Answer 2

Try the following:

 def geoLocationId = column[Option[Int]]("geo_location_id") //Foreign Key def geoLocation = foreignKey("fk_geo_location", geoLocationId, GeoLocations)(_.id.?) 

geoLocationId now an Option[Int] column, so O.Nullable no longer required (_.id.?) returns GeoLocation as an option or None if this value is null.