Squeryl: Nullable foreign key

82 views Asked by At

I am trying to declare a schema, where some entity has recursive relation (property parent_id may be either NULL or some value from the same table):

class Merchant(
  val id: Int = 0,
  @Column("parent_id") var parentId: Option[Int] = None    
) extends KeyedEntity[Int] {
  def this() = this(0, Some(0))
}

in schema:

val merchants: Table[Merchant] = table[Merchant]
on(merchants)(m => declare(
  m.id is autoIncremented
))

val parent2merchants = oneToManyRelation(merchants, merchants).via((p, m) => p.id === m.parentId)

The table gets created, and column parent_id int (allows NULLs).

But when I'm trying to add records leaving parentId = None, I get an error:

Referential integrity constraint violation: "MERCHANTSFK1: PUBLIC.MERCHANTS FOREIGN KEY(PARENT_ID) REFERENCES PUBLIC.MERCHANTS(ID)"; SQL statement:
insert into merchants (parent_id) values (?) [23002-127]
errorCode: 23002, sqlState: 23002    
jdbcParams:[0]

So, for some reason, parentId instead of NULL gets 0. What am I doing wrong?

0

There are 0 answers