SlickReadSide: createIfNotExists doesn't create foreign key constraints

Lagom
1

Hello,

In a small application that I am trying to build (https://github.com/codingkapoor/simple-lms-platform), I am using slick to build read side persistence.

In order to pace up my development, I use Lagom’s “setGlobalPrepare” method on ReadsideHandler builder to create tables on mysql for me on startup.

.setGlobalPrepare(
  employeeRepository.createTable andThen 
  intimationRepository.createTable andThen 
  requestRepository.createTable
)

But to my surprise if I use createIfNotExists api on schema to create tables, the foreign key constraints are not getting created. It is only if I change it to create are the foreign key constraints getting created.

simple-lms-platform/employee-impl/src/main/scala/
com/codingkapoor/employee/persistence/read/dao/intimation/
IntimationRepository.scala

simple-lms-platform/employee-impl/
src/main/scala/com/codingkapoor/employee/persistence/read/dao/intimation/
IntimationTableDef.scala

// doesn't create foreign key constraints
 def createTable: DBIO[Unit] = intimations.schema.createIfNotExists

// creates foreign key constraints
 def createTable: DBIO[Unit] = intimations.schema.create

Which I verfiy on mysql like so:

mysql> show create table intimation
    -> ;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                        |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| intimation | CREATE TABLE `intimation` (
  `EMP_ID` bigint(20) NOT NULL,
  `REASON` text NOT NULL,
  `LATEST_REQUEST_DATE` date NOT NULL,
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

mysql> show create table intimation;
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                    |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| intimation | CREATE TABLE `intimation` (
  `EMP_ID` bigint(20) NOT NULL,
  `REASON` text NOT NULL,
  `LATEST_REQUEST_DATE` date NOT NULL,
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`),
  KEY `EMP_FK` (`EMP_ID`),
  CONSTRAINT `EMP_FK` FOREIGN KEY (`EMP_ID`) REFERENCES `employee` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Please let me know if this is a bug or am I making any mistakes here. Thanks

2

So, not an answer, but additional information.
I was using createIfNotExists on a table/primary key combination (with H2). The primary key got created when createIfNotExists ran the first time, but failed on the second run with the error “primary key already exists”. Looks like the “if not exists” check was only done on the table.
This defect covers my situation:

3

But in my case the foreign key constraint is not getting created even at the first time.

4

Created an issue in github: https://github.com/lagom/lagom/issues/2415.

5

The Lagom 1.5 and 1.6 docs suggest using .createIfNotExist for creating schemas.
The Lagom 1.4 had a different workaround which no longer works with Slick 3.3.2.
I showed a workaround that I use with Lagom 1.6 & Slick 3.3.2 here: