Exposed: Foreign key constraints not working in SQLite

[MichaelKofler]

I created a sample database where the Albums table references the Artist table. I am using SQLite. I can delete an artist even when it is referenced from the Albums table. (With H2, this fails as it should with a ForeignKey exception.) I tried to set PRAGMA foreign_keys = ON, but this does not help either. Do I miss some hidden option?

Test code.

object Artists : IntIdTable() {
    val name = varchar("name", length = 100)
}
object Albums : IntIdTable() {
    val title = varchar("title", 250)
    val artistId = reference("artistId", Artists)
    // onDelete = ReferenceOption.CASCADE,
    // onUpdate = ReferenceOption.CASCADE)
}

fun testSQLiteFK() {
    File("fktest.db").delete()
    Database.connect("jdbc:sqlite:fktest.db", "org.sqlite.JDBC")
    TransactionManager.manager.defaultIsolationLevel =
       Connection.TRANSACTION_SERIALIZABLE

    transaction {
        addLogger(StdOutSqlLogger)
        SchemaUtils.create(Artists)
        SchemaUtils.create(Albums)
        val pfId = Artists.insertAndGetId { it[name] = "Pink Floyd" }
        Albums.insert {
            it[title] = "Dark side of the moon"
            it[artistId] = pfId
        }
    }
    transaction {
        addLogger(StdOutSqlLogger)
        // should fail because of FK constraint
        Artists.deleteWhere { Artists.name eq "Pink Floyd" }
    }
    transaction {
        addLogger(StdOutSqlLogger)

        // explicitly enabling foreign keys (?)
        // does not help
        val sql = "PRAGMA foreign_keys = ON"
        connection.executeInBatch(listOf(sql))

        for (row in Artists.selectAll())
            println(row)
        // no output

        for (row in Albums.selectAll())
            println(row)
        // Albums.id=1, Albums.title=Dark side of the moon, Albums.artistId=1
        // refers to no longer existing artist
    }
}

// SQL logging
// SQL: CREATE TABLE IF NOT EXISTS Artists (id INTEGER PRIMARY KEY AUTOINCREMENT, "name" VARCHAR(100) NOT NULL)
// SQL: CREATE TABLE IF NOT EXISTS Albums (id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(250) NOT NULL, artistId INT NOT NULL, CONSTRAINT fk_Albums_artistId_id FOREIGN KEY (artistId) REFERENCES Artists(id) ON DELETE RESTRICT ON UPDATE RESTRICT)
// SQL: INSERT INTO Artists ("name") VALUES ('Pink Floyd')
// SQL: INSERT INTO Albums (artistId, title) VALUES (1, 'Dark side of the moon')
// SQL: DELETE FROM Artists WHERE Artists."name" = 'Pink Floyd'
// SQL: PRAGMA foreign_keys = ON
// SQL: SELECT Artists.id, Artists."name" FROM Artists
// SQL: SELECT Albums.id, Albums.title, Albums.artistId FROM Albums

[Tapac]

Could you try to execute PRAGMA foreign_keys = ON before DELETE.
Also, you could use Transaction.exec(sql) (or just exec(sql) within a transaction block) to execute raw queries.

[MichaelKofler]

Thank's for your reply. In my sample code, exec was misplaced, sorry. However, even if I place exec("PRAGMA foreign_keys = ON") right before deleteWhere(...), the record is deleted without an foreign key error. (BTW, shouldn't FK rules be enabled by default?)

[MichaelKofler]

wrong button, sorry

[kovbe11]

I have the same issue. When I open the connection I use this code:

        transaction {
            addLogger(StdOutSqlLogger)
            exec("PRAGMA foreign_keys = ON;")
            exec("PRAGMA foreign_keys = ON")
        }

(At this point I'm not sure if I have to use semicolons or not, so I do both just in case)

But then if I delete an item sth like this:

        transaction {
            addLogger(StdOutSqlLogger)
            exec("PRAGMA foreign_keys = ON")
            exec("PRAGMA foreign_keys = ON;")
            item.delete()
        }

It gets deleted without any problems..

[connected-rmcleod]

I was also having this problem and for some reason doing exec("PRAGMA foreign_keys = ON") in a transaction never works, but this does:

Database.connect("jdbc:sqlite:data.db", "org.sqlite.JDBC", setupConnection = { connection ->
        connection.createStatement().executeUpdate("PRAGMA foreign_keys = ON")
})

[Tapac]

@connected-rmcleod , @kovbe11 , @MichaelKofler , as I understand from documentation foreign_keys should be enabled on connection level and PRAGMA will have no effect if it wasn't enabled.
Could you please check your code with adding ?foreign_keys=on to connection url like:
"jdbc:sqlite:data.db?foreign_keys=on"
?

[MichaelKofler]

indeed, "jdbc:sqlite:data.db?foreign_keys=on" is the solution. deleteWhere in my sample code now causes

SQLiteException: [SQLITE_CONSTRAINT] Abort due to constraint violation (FOREIGN KEY constraint failed)

which is the correct behavior

[connected-rmcleod]

@Tapac _Ohhh_ thanks. I'm new to this library and didn't know I could do it in the URL. Can confirm, works with this change.

[Tapac]

Hurray! Then I'll close the issue.