How to define optional 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 my case class is:

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

As you notice geoLocation is an optional foreign key....

I can't find any way to describe this "Optional" in my foreign key definition.

I've tried like:

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

but I receive:

Caused by: scala.slick.SlickException: Cannot use column Apply Function Cast in foreign key constraint (only named columns are allowed)

Does anybody has a suggestion?

Answer 1

Try The following:

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

geoLocationId is now a Column of Option[Int] therefore O.Nullable is no longer needed (_.id.?) returns the GeoLocation as an option, or None if it was null.

Answer 2

I don't think that what you are trying to do is accomplishable by using foreign keys. Check out joining and user defined types from the Slick docs.

Note the example with the leftJoin:

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

So if you wanted to query for all of your Addresses, you'd want to 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 could then map the results of that query so that you get back an actual Address instance, complete with an Option[GeoLocation]. That's why I linked the "user defined types" in the docs... that's a new feature to me (I was familiar with ScalaQuery, which was Slick's previous incarnation), but it looks fairly promising.