Skip to content

Support Table Foreign Key Constraints in SQLite#945

Merged
parsonsmatt merged 2 commits intoyesodweb:masterfrom
kderme:sqlite-foreign
Aug 20, 2019
Merged

Support Table Foreign Key Constraints in SQLite#945
parsonsmatt merged 2 commits intoyesodweb:masterfrom
kderme:sqlite-foreign

Conversation

@kderme
Copy link
Contributor

@kderme kderme commented Aug 19, 2019 •
edited by parsonsmatt
Loading

This pr adds support for foreign keys, table constraints in SQLite. They can only reference Primary keys of other tables and not Unique keys, since this would require changes in Quasi and I'm not sure how well this is supported in other dbs. I've tested migrations from existing schemas, which adds new foreign keys to existing Tables and it also works fine.

Before submitting your PR, check that you've:

After submitting your PR:

  • Update the Changelog.md file with a link to your PR
  • Check that CI passes (or if it fails, for reasons unrelated to your change, like CI timeouts)

@parsonsmatt
Copy link
Collaborator

parsonsmatt commented Aug 19, 2019

Code looks good to me.

They can only reference Primary keys of other tables and not Unique keys, since this would require changes in Quasi and I'm not sure how well this is supported in other dbs.

Can you elaborate?

@kderme
Copy link
Contributor Author

kderme commented Aug 19, 2019 •
edited
Loading

Sure! What I mean is that this migration works fine with this pr

share [mkPersist sqlSettings, mkSave "entityDefs"] [persistLowerCase|
Person
    name String
    lastNameId Int
    Primary name lastNameId
    deriving Eq Show

Car
   owner String
   ownerId Int
   color String
   Foreign Person belongs owner ownerId
|]

but this


share [mkPersist sqlSettings, mkSave "entityDefs"] [persistLowerCase|
Person
    name String
    lastNameId Int
    UniquePerson name lastNameId
    deriving Eq Show

Car
   owner String
   ownerId Int
   color String
   Foreign Person belongs owner ownerId
|]

still fails with

     • Exception when trying to run compile-time code:
        no explicit primary key fdef=ForeignDef ...

Such a migration is accepted by SQLite (actually now that I checked again, SQLites accepts it even without the unique constraint).

@parsonsmatt
Copy link
Collaborator

parsonsmatt commented Aug 19, 2019

Right, thanks. I'm good to accept and merge this as-is then if you want to bump the relevant version numbers and add a changelog entry 😄

@parsonsmatt
Copy link
Collaborator

parsonsmatt commented Aug 20, 2019

CI is passing, merging and releasing. Thanks!

@parsonsmatt parsonsmatt merged commit 72e4dd8 into yesodweb:master Aug 20, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

2 participants

@kderme @parsonsmatt