Sqlite.swift: Unable to set foreign key

[kylejm]

I'm trying to setup a foreign key with an ON UPDATE and ON DELETE setting, but the call I make seems to have no effect.

Here's my code:

db.foreignKeys = true

let categories = db["Categories"]
let categoryID = Expression<Int64>("id")
et name = Expression<String>("name")

db.create(table: categories) { t in
    t.column(categoryID, primaryKey: .Autoincrement)
    t.column(name)
}

let bookmarks = db["Bookmarks"]
let bookmarkID = Expression<Int64>("bookmark_id")
let url = Expression<String>("url")
let comment = Expression<String?>("comment")
let categoryFK = Expression<Int64?>("category_id")

db.create(table: bookmarks) { t in
    t.column(bookmarkID, primaryKey: .Autoincrement)
    t.column(name)
    t.column(url)
    t.column(comment)
    t.column(categoryFK)
    t.foreignKey(categoryFK, references: categories[categoryID], update: SchemaBuilder.Dependency.Cascade, delete: SchemaBuilder.Dependency.Cascade)
}

Am I using the foreighKey function on the last line, correctly? No relationship is setup by the end of the this code being executed.

Many thanks in advance, and awesome job on SQLite.swift!

[stephencelis]

That line looks right to me. What makes you think the relationship isn't getting set up?

Try prepending the code above with db.trace(println) to log the queries.

You can also shorten that last line by using the dot-abbreviated form:

t.foreignKey(categoryFK, references: categories[categoryID], update: .Cascade, delete: .Cascade)

Also: note that _every_ connection needs to run db.foreignKeys = true for foreign key constraints to work.

[kylejm]

Wow, thanks for the quick reply!

Ah that's so awesome! From the printouts I can see that the correct statement is being written. The thing that made me think that the foreign key wasn't being setup was that Base wasn't showing the foreign key. Is this something you'd expect or see in a client you use? Am I thinking of constraints in the wrong way perhaps? I can share screenshots if it helps. Using:

t.column(categoryFK, references: categories[categoryID])

works and shows in Base.

Cool, thanks for the tip.

Ah cool, I put that in when using t.column(, references:) as an exception was raised without it.

[stephencelis]

I wonder if Base has a bug in how it looks for foreign keys? Can you query the sqlite_master table to verify?

[kylejm]

Yep, it's there:

So I'm good?

[stephencelis]

I'd test with some dummy data, but you should be good to go.

[kylejm]

Awesome, BRB

[kylejm]

Hold tight, I'll have to look at this when I get back home in a few hours to confirm it works fine

[kylejm]

Tested. All good. Weird that it's not showing on Base. I'll shoot them an email to give them a heads up. Thanks Stephen

[satishVekariya]

@stephencelis
unable to set db.foreignKeys = true !!!

Value of type 'Connection?' has no member 'foreignKeys'