Yesod / Persistent中的外键约束？ [英] Foreign key constraints in Yesod/Persistent?

问题描述

我尝试使用Database.Persistant为Scotty应用程序创建数据库，但我无法弄清楚在表之间添加外键约束的语法。例如，我有一个 User 表和一个 Post 表，我希望 Post 表中有一个属性 authorId ，它引用 UserId 在用户。这可以很容易地在原始SQL中完成，但是我希望能够通过haskell访问数据而不诉诸原始的sql命令。此外，约束将覆盖数据库迁移。这是我现在定义数据库的方式：



  share [mkPersist sqlSettings，mkMigratemigrateAll] 
 [persistLowerCase | 
用户
名称字符串
电子邮件字符串
用户名字符串
主要用户名
派生显示
发布
标题字符串
内容T.Text 
作者字符串
派生显示
 |] 
  

这很好，但没有关键限制，这可能是一件非常糟糕的事情。
如果我尝试添加外键约束，如维基在github上表示，通过将外部用户authorfk作者添加到 Post 块中，它编译得很好，但没有任何发生;没有迁移发生，也没有引入外键约束。

我做错了什么？任何帮助或建议，将不胜感激。

要清楚，我想要的是Post中的作者属性引用用户中的现有用户名。

解决方案

Persistent 使用Haskell类型系统来生成外键。这就是为什么没有特定的字段类型来指示一个字段引用另一个表中的记录。

您应该使用 Persistent
$ b 说我有用户和 >文章表格。 Persistent 会为您生成 UserId 和 ArticleId 。然后，您将使用它们来指示如下示例中的引用：



 用户
用户名文本
密码文本
电子邮件文本
说明文字也许
活动Bool 
 
 UniqueUser用户名
 UniqueEmail电子邮件
 
派生类型
 
 Article 
 artname文本
标题文本
关键字文本也许
描述文本也许
机构Markdown 
父项ArticleId也许 - 可选外键
用户UserId  - 所需外键
 lastUpdate UTCTime 
权重Int 
 public Bool 
 
唯一艺术artname 
 
派生类型
  

这个模型说：



• Article 可能持有对 Article的引用，与父字段类型 ArticleId也许 code $。
Article 必须包含对 User 的引用c>用用户字段输入 UserId 。



这个例子将在PostgreSQL中生成以下 article 表：

 表格public.article
 Column |类型|修饰符
 ------------- + -------------------------- + ---- ------------ 
 id |整数|不为空（...）
 artname |字符变化|不为空
 title |字符变化|不为空
 body |字符变化| not null 
 parent | bigint | 
 user | bigint |不为空
 last_update |带时区的时间戳|不为空
重量| bigint |不为空
 public |布尔|不为空
关键字|字符变化| 
描述|字符变化| 
 $ b $索引：
article_pkeyPRIMARY KEY，btree（id）
unique_artUNIQUE CONSTRAINT，btree（artname）
外键约束：
article_parent_fkeyFOREIGN KEY（父）
 REFERENCES文章（id）
article_user_fkeyFOREIGN KEY（user）
 REFERENCESuser（id）
引用方： 
 TABLEarticleCONSTRAINTarticle_parent_fkey
 FOREIGN KEY（父）
参考文章（id）
  

注意：如果您使用SQLite，则必须确保已启用外键支持。请参阅→ SQLite外键支持：启用外键支持

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

这篇关于Yesod / Persistent中的外键约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！