用一个查询从3个表中删除 [英] delete from 3 tables with one query

问题描述

我有3个表，我不想在表中定义任何外键.我的表结构如下:表格图

i have 3 tables and i dont want define any foreign key in my tables. my tables structure are like below: tables diagram

我写了这个查询:

delete relativedata, crawls, stored
    from relativedata inner join
         crawls
         on relativedata.crawl_id = crawls.id and
            relativedata.id = ? inner join
         stored
         on stored.crawl_id = crawls.id

该查询对我有用，除非其中一个表没有记录.现在如何在1个查询中的3个表中执行此删除操作?

this query works for me unless one of tables has no records. now how can i do this delete in 3 tables in 1 query?

推荐答案

如果所有表都具有记录，则该方法可行，请尝试使用INNER JOIN的LEFT JOIN instread.另外，您对join的ON条件有些混乱.像这样尝试:

If it works if all tables have records, try using LEFT JOIN instread of INNER JOIN. Also, You had some mess with Your joins ON conditions. Try it like this:

delete 
    relativedata, crawls, stored
from
    relativedata 
LEFT join crawls on relativedata.crawl_id = crawls.id
LEFT join stored on relativedata.crawl_id = stored.crawl_id
WHERE
    relativedata.id = ? 

此外，forgin密钥是一件好事，并且不使用它们通常不是一个好主意.是的，一开始他们似乎很烦人，但是当他们惹恼您时，请尝试集中精力.在大多数情况下，当您以您不应该使用的方式干预数据时，他们就会这样做，并且没有它们，您会导致DB中的数据成本不高.

Also, foregin keys are good thing, and not using them is generally bad idea. Yes, they seems to be annoying at first, but try to focus on WHEN they annoy You. Most of the times they do it when You are meddling with data in a way You should not, and without them You wloud cause data incostincency in Your DB.

但是，这只是我的意见.

But, it is just my opinion.

这篇关于用一个查询从3个表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！