主页 / dba / 问题 / 164821
Accepted
Hassan Baig
Asked: 2017-02-20 03:19:27 +0800 CST

从包含指向彼此的 FK 的表中删除记录

  • 772

背景：这里有点意外的 DBA。我有这个类似 reddit 的网站，用户可以在其中提交指向各种互联网内容的链接，然后可以在每个帖子下留下评论。这个应用程序——我们称之为链接——有两个对应的表来存储数据：link和publicreply（即评论关联到每个链接）。

问题：由于相互依赖的 FK 约束，我似乎无法从这两个表中删除记录（用于维护）。需要指导来解决这种情况。

详细信息：每个对象都为其关联Publicreply的对象存储一个 FK 。Link此外，每个Link对象还保存对与其关联的最新公共回复的引用。这会造成所有Publicreply对象都有LinkFK 的情况，反之亦然。如：

Table "public.links_link"
        Column        |           Type           |                        Modifiers                        
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 url                  | character varying(250)   | not null
 image_file           | character varying(100)   | 
 reply_count          | integer                  | default 0
 latest_reply_id      | integer                  | 
 is_visible           | boolean                  | default true
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED

 Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

在这种情况下如何从表中删除记录Link？而从Publicreply？我正在使用 postgresql 9.3.10。

postgresql postgresql-9.3

1 个回答

  1. Best Answer

    该问题可以通过多种方式解决：

    1. 首先，我们注意到其中一个 FK 列是可以为空的。这允许从两个表中删除，在单个事务中使用三个语句并且不需要可延迟的约束。首先更新latest_reply_id为 null，然后从 删除PublicReply，然后从Link：

      -- Delete one (or more) `Link` rows 
-- and all the `Publicreply` rows associated with them:
BEGIN ;
    UPDATE public.links_link
    SET latest_reply_id = NULL
    WHERE id IN (?, ?, ..., ?) ;              -- Link ids to be deleted

    DELETE FROM public.links_publicreply
    WHERE answer_to_id IN (?, ?, ..., ?) ;    -- Link ids to be deleted

    DELETE FROM public.links_link
    WHERE id IN (?, ?, ..., ?) ;              -- Link ids to be deleted
COMMIT ;

    2. 然后我们注意到外键约束之一已经被定义为可延迟的。这允许从两个表中删除，在单个事务中使用两个语句：

      -- Delete one (or more) `Link` rows 
-- and all the `Publicreply` rows associated with them:
BEGIN ;
    DELETE FROM public.links_link
    WHERE id IN (?, ?, ..., ?) ;              -- Link ids to be deleted

    DELETE FROM public.links_publicreply
    WHERE answer_to_id IN (?, ?, ..., ?) ;    -- Link ids to be deleted
COMMIT ;

    3. 使用可修改的 CTE，我们可以在单个语句中从两个表中删除。不需要为此推迟约束。例子：

      -- Delete one (or more) `Link` rows 
-- and all the `Publicreply` rows associated with them:
WITH del_link AS
  ( DELETE FROM public.links_link
    WHERE id IN (?, ?, ..., ?)                -- Link ids to be deleted
    RETURNING id
  )
DELETE FROM public.links_publicreply
WHERE answer_to_id IN (TABLE del_link) ;

    删除PublicReply会稍微复杂一些，具体取决于要求，但可以使用上述任何一种方法来完成。有什么要求？

    • 删除 a PublicReply，它的父级Link和所有相关的回复？

    • 删除一个PublicReply，如果它是最新的回复，将父级更改Link为指向上一个回复？如果它是唯一的，请将其设置为 NULL？

    • 删除一个PublicReply，如果它是最新的回复，将父级更改Link为指向上一个回复？如果是唯一的，也要删除父Link项吗？

    • 8

相关问题