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"] [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.
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