Foreign key constraints in Yesod/Persistent?

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"]
     name String
     email String
     username String
     Primary username
     deriving Show
     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.


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:

    username    Text
    password    Text
    email       Text
    description Text Maybe
    active      Bool

    UniqueUser  username
    UniqueEmail email

    deriving    Typeable

    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        |

    "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

Need Your Help

To continually pull result from the server via Ajax until timeout or getting result

jquery ruby ajax prototype ruby-on-rails

I would like to make an Ajax request to the server, which replies a job status and result. If the status is "Error" or "Ok", the client should show the result (containing HTML code) to the user. Ho...

Implementing SessionAuthentication with django-rest-framework and Ajax?

ajax django django-rest-framework

I have a RESTful API that only supports GET requests. It is running on the same domain as my front-end. All this is in Django 1.8 and DRF 3.2.2.