Is It Possible To Handle Invalid References (SQL Server)?

Question

I've very much enjoyed being able to shorten my code by using reference in my Table objects as well as referencedOn and referrersOn in my Entities. But as I've gotten further with my project, I've realized that I might have to undo all that work and recreate these processes manually in the event that the rows being referenced are deleted.

Is there any way of keeping these without risking IllegalStateExceptions (like being able to provide a default foreign key you know exists), or will I have to give it all up for manual reference methods?

Here is a minimal example:

fun main(args: Array<String>) {
    /* Connecting to DB here */
    transaction {
        val timesheets = TimeSheet.all()
        println("Printing time sheet list:")
        timesheets.forEach { sheet ->
            println("Time Sheet Object for employee ${sheet.employee}\n$sheet")
        }
    }
    /* Disconnecting to DB */
}

object EmployeeTable : IdTable<UUID>("employeeTable") {
    override val id = uuid("EmployeeUID").entityId().primaryKey()
}

object TimeSheetTable : IdTable<Int>("timeSheetTable") {
    override val id = integer("JobUID").primaryKey().autoIncrement().entityId()

    val employee = reference("EmployeeUID", EmployeeTable)
}

class Employee(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<Employee>(EmployeeTable)
}

class TimeSheet(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<TimeSheet>(TimeSheetTable)

    var employee by Employee referencedOn TimeSheetTable.employee
}

Once the list of time sheets tries to print a row where a Employee UID doesn't match any Employee rows, it'll throw:

Exception in thread "main" java.lang.IllegalStateException: Cannot find employeeTable WHERE id=some-invalid-id-string

Could you provide more details or sample code where you got `IllegalStateExceptions` ? — Tapac, Dec 08 '19 at 14:03
Sorry for the late reply, but how it's possible what your TimeSheet doesn't have a linked Employee while your reference is not optional? If you expect what TimeSheet could be created without an employee then define it with optReference/optionalReferencedOn. — Tapac, Dec 24 '19 at 16:32
Does optional optReference/optionalReferencedOn help with invalid references? This is separate than having a NULL reference. — Steph, Dec 24 '19 at 16:51
What is `invalid reference` ? If you have a foreign key you can only have a reference or not. Do you have tables without foreign keys? — Tapac, Dec 25 '19 at 21:42
Yes, the table does not have foreign keys. The database was managed before I came to work at my job. Apparently, the previous programmer did not link the tables and seemed to delete rows from the source table without cleaning up the other tables that held a reference. I can't just delete those rows though, they still hold important information for my company (like total hours worked in a project, necessary for charging customers). So is doing table joins my only option? — Steph, Dec 26 '19 at 20:52
My advice is to: 1. Make your reference column nullable in a database (if it's not so) 2. Set column value to null where ref.records are absent with SQL 3. Add foreign key constraint to a table. 4. Use optReference in Exposed. — Tapac, Dec 28 '19 at 07:52

Is It Possible To Handle Invalid References (SQL Server)?

0 Answers0