Foreign key constraints in Yesod/Persistent?

Question

I am trying to use Database.Persistant to make a database for a Scotty app, and I cannot figure out the syntax for adding a foreign key constraint between tables. For example, I have a User table and a Post table, and I want the Post table to have an attribute authorId which references UserId in User. This can be accomplished quite easily in raw SQL, but I want to be able to access the data through haskell without resorting to raw sql commands. Also, the constraints would be overwritting upon database migrations. This is what I have at the moment to define the database:

share [mkPersist sqlSettings, mkMigrate "migrateAll"]
[persistLowerCase|
   User
     name String
     email String
     username String
     Primary username
     deriving Show
   Post
     title String
     content T.Text
     author String
     deriving Show
|]

This is fine, but has no key constraints, which can be a very bad thing. If I try to add a foreign key constraint like the wiki on github says, by adding the line Foreign User authorfk author to the Post block, it compiles fine, but nothing happens; no migration takes place and no foreign key constraint is introduced.

What am I doing wrong? Any assistance or advise would be greatly appreciated.

To be clear, what i want is the author attribute in Post to reference an existing username in User.

Answer 1

Persistent uses the Haskell type system to generate foreign keys. That’s why there is no specific field type to indicate a field references a record in another table.

You should use the key type that Persistent created automatically to indicate the key.

Say I have User and Article tables. Persistent will generate the UserId and ArticleId for you. You will then use them to indicate references like in this example:

User
    username    Text
    password    Text
    email       Text
    description Text Maybe
    active      Bool

    UniqueUser  username
    UniqueEmail email

    deriving    Typeable

Article
    artname     Text
    title       Text
    keywords    Text Maybe
    description Text Maybe
    body        Markdown
    parent      ArticleId Maybe   -- optional Foreign Key
    user        UserId            -- required Foreign Key
    lastUpdate  UTCTime
    weight      Int
    public      Bool

    UniqueArt   artname

    deriving    Typeable

This model says:

• An Article may hold a reference to another Article with the parent field of type ArticleId Maybe.
• An Article must hold a reference to a User with the user field of type UserId.

This example will generate the following article table in PostgreSQL:

                Table "public.article"
   Column    |           Type           |    Modifiers
-------------+--------------------------+----------------
 id          | integer                  | not null (...)
 artname     | character varying        | not null
 title       | character varying        | not null
 body        | character varying        | not null
 parent      | bigint                   | 
 user        | bigint                   | not null
 last_update | timestamp with time zone | not null
 weight      | bigint                   | not null
 public      | boolean                  | not null
 keywords    | character varying        |
 description | character varying        |

Indexes:
    "article_pkey" PRIMARY KEY, btree (id)
    "unique_art" UNIQUE CONSTRAINT, btree (artname)
Foreign-key constraints:
    "article_parent_fkey" FOREIGN KEY (parent)
                          REFERENCES article(id)
    "article_user_fkey" FOREIGN KEY ("user")
                        REFERENCES "user"(id)
Referenced by:
    TABLE "article" CONSTRAINT "article_parent_fkey"
                    FOREIGN KEY (parent)
                    REFERENCES article(id)

Note: If you use SQLite, you must ensure that foreign keys support is enabled. See → SQLite Foreign Key Support: Enabling Foreign Key Support