Slick 3 - ForeignKeys are not generated

Question

I cannot seem to find a solution to this weird bug:

class Names(tag: Tag) extends Table[Name](tag, "NAME") with Identifiable[Name]{
  def firstName = column[String]("firstName")
  def lastName = column[String]("lastName")
  def profileId = column[Int]("profileId")
  def * = (id.?, firstName, lastName, profileId) <> ((Name.apply _).tupled, Name.unapply)
  def profileFk = foreignKey("profile_fk", profileId, TableQuery[Profiles])(_.id, onDelete=ForeignKeyAction.Cascade)
}

class PhoneNumbers(tag: Tag) extends Table[PhoneNumber](tag, "PHONENUMBER") with Identifiable[PhoneNumber] {
  def number = column[String]("number")
  def kind = column[String]("kind")
  def profileId = column[Int]("profileId")
  def * = (id.?, number, kind, profileId) <> ((PhoneNumber.apply _).tupled, PhoneNumber.unapply)
  def profileFk = foreignKey("profile_fk", profileId, TableQuery[Profiles])(_.id, onDelete=ForeignKeyAction.Cascade)
}

and the profile class consists of an id field only. Identifiable provides the id-property.

I am using Slick 3 with MySQL.

For Names a foreignKey to Profiles is generated, for PhoneNumbers not. Why? There seems to be no difference?

Update:

Here the relevant statements:

create table `PHONENUMBER` (`id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,`number` TEXT NOT NULL,`kind` TEXT NOT NULL,`profileId` INTEGER NOT NULL)

And:

alter table `PHONENUMBER` add constraint `profile_fk` foreign key(`profileId`) references `PROFILE`(`id`) on update NO ACTION on delete CASCADE

Complete Output:

create table PROFILE (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,userId INTEGER NOT NULL)

create table VERSION (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,timestamp INTEGER NOT NULL,vector INTEGER NOT NULL)

create table NAME (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,firstName TEXT NOT NULL,lastName TEXT NOT NULL,profileId INTEGER NOT NULL,versionId INTEGER NOT NULL)

create table PHONENUMBER (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,number TEXT NOT NULL,kind TEXT NOT NULL,profileId INTEGER NOT NULL,versionId INTEGER NOT NULL)

create table VIEW (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) create table PHONENUMBERS_VIEWS (phoneNumber INTEGER NOT NULL,view INTEGER NOT NULL)

create table CREDENTIALS (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,username TEXT NOT NULL,password TEXT NOT NULL,userId INTEGER NOT NULL)

create table USER (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)

create table API_KEY (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,token TEXT NOT NULL,deviceId TEXT NOT NULL,credentialsId INTEGER NOT NULL)

alter table PROFILE add constraint user_fk foreign key(userId) references USER(id) on update NO ACTION on delete NO ACTION

alter table NAME add constraint profile_fk foreign key(profileId) references PROFILE(id) on update NO ACTION on delete CASCADE

alter table NAME add constraint version_fk foreign key(versionId) references VERSION(id) on update NO ACTION on delete NO ACTION

alter table PHONENUMBER add constraint profile_fk foreign key(profileId) references PROFILE(id) on update NO ACTION on delete CASCADE

alter table PHONENUMBER add constraint version_fk foreign key(versionId) references VERSION(id) on update NO ACTION on delete NO ACTION

alter table PHONENUMBERS_VIEWS add constraint phoneNumber_fk foreign key(phoneNumber) references PHONENUMBER(id) on update NO ACTION on delete NO ACTION alter table PHONENUMBERS_VIEWS add constraint view_fk foreign key(view) references VIEW(id) on update NO ACTION on delete NO ACTION

alter table CREDENTIALS add constraint user_fk foreign key(userId) references USER(id) on update NO ACTION on delete NO ACTION

alter table API_KEY add constraint credentials_fk foreign key(credentialsId) references CREDENTIALS(id) on update NO ACTION on delete NO ACTION

` symbol is hidden because of markdown, but there in the original output

EDIT 2:

val profiles = TableQuery[Profiles]
val names = TableQuery[Names]
val phoneNumbers = TableQuery[PhoneNumbers]
val views = TableQuery[Views]
val phoneNumbersToViews = TableQuery[PhoneNumbersToViews]
val users = TableQuery[Users]
val credentials = TableQuery[CredentialsSchema]
val apiKeys = TableQuery[ApiKeys]
val versions = TableQuery[Versions]

val schema = profiles.schema ++
  versions.schema ++
  names.schema ++
  phoneNumbers.schema ++
  views.schema ++
  phoneNumbersToViews.schema ++
  credentials.schema ++
  users.schema ++
  apiKeys.schema

SlickDB().run(DBIO.seq(
  schema.create
))

schema.createStatements.foreach(println)

Did you created the added the profileFk after your table was generated ? I will try to drop the table and will recreate again. — Biswanath
Can post your table create statements and drop statements ? Also lets us know if you solved this problem. — Biswanath
slick.typesafe.com/doc/2.1.0/…. See the bottom, use createstatements and dropstatements and to print the generated sql. — Biswanath
The only other reason, I will verify is if you are crating any table with myISAM engine. myISAM does not support foreign key as such will not throw any error if issue a creation of foreign key, if the syntax is right. If thats not the case please post your scala code where you create and drop tables. — Biswanath
FYI, Your create statements contains two foreign key constraints with same name "profile_fk",but in code they are named as "profile_fk" and "profileA_fk" — Biswanath

Richard Dallaway Richard Dallaway · Accepted Answer · 2015-05-30T15:37:31

"For Names a foreignKey to Profiles is generated, for PhoneNumbers not. Why? There seems to be no difference?"

I think the lack of a difference is the problem here.

You've used the same foreign key name ("profile_fk") for Names.profileFk and PhoneNumbers.profileFk. The MySQL foreign key names need to be unique.

I'd suggest replacing "profile_fk" with "name_profile_fk" and "phonenumber_profile_fk".

However, you should be seeing an exception. The first of the alter table statements you included...

alter table NAME add constraint profile_fk foreign key(profileId) references PROFILE(id) on update NO ACTION on delete CASCADE

... should run OK, but the second...

alter table PHONENUMBER add constraint profile_fk foreign key(profileId) references PROFILE(id) on update NO ACTION on delete CASCADE

...is where there should be an exception.

If you're not seeing an exception, it could be that your application is terminating before the future you execute returns. You can test that out by placing an await around your SlickDB().run command. E.g.,

import scala.concurrent.Await
import scala.concurrent.duration._
Await.result(
  SlickDB().run(DBIO.seq(schema.create))
  , 10 seconds)

Slick 3 - ForeignKeys are not generated

1 Answers