php - 外键违规 : 7 ERROR

Question

我在这方面确实是新手。问题是...我有文章网站。人们可以在那里对文章进行评分。如果没有人评价，我可以删除文章。但如果有人对文章进行评分，我就会不断收到以下错误:

PDOException: SQLSTATE[23503]: Foreign key violation: 7 ERROR:  update or delete on table "article" violates foreign key constraint "article_rating_item_id_fkey" on table "article_ratings"
DETAIL:  Key (id)=(xxxx) is still referenced from table "article_ratings". in /libs/Nette/loader.php:3515  @  http://www.xxxxxx/admin/articleedit/3578?do=deletearticle  @@  exception-2014-09-29-18-14-37-b625334b3e569cb7661f1704256874c1.htm

当我检查该文件时，有以下代码:

public function handleDeletearticle($id)
        {
            $article = $this->context->createArticles()->get($id);
            $this->context->createArticles()->where("id", $id)->delete();
            $this->flashMessage('Done', 'success');
            $this->redirect('Admin:articles');
        }

你能帮我解决这个问题吗？预先感谢您

编辑:这就是 Articles.php 的样子

    public function selectArticleWithRating($slug)
{
    $article = $this->query("Select article.*, COUNT(rating.id) AS plus, COUNT(rating2.id) AS minus, \"user\".avatar, \"user\".username
     FROM article
     LEFT JOIN rating AS rating ON rating.item_id=article.id and rating.type='article' and rating.rate=1
     LEFT JOIN rating AS rating2 ON rating2.item_id=article.id and rating2.type='article' and rating2.rate=0
     LEFT JOIN \"user\" ON \"user\".id=article.user_id
     WHERE slug='$slug'
     GROUP BY article.id, \"user\".id");

    return $article;
}

不应该有article_ ratings吗？

Answer 1

在您收到的错误消息中，确实表明您存在外键引用冲突。这意味着您正在尝试删除数据库中某处引用的行，它甚至会告诉您在哪里:

is still referenced from table "article_ratings"

您也可以使用ON DELETE CASCADE删除引用行 http://www.mysqltutorial.org/mysql-on-delete-cascade/

SO 上有一个涉及此问题的问题:MySQL on delete cascade. Test Example

这里有一个很好的解释:https://dba.stackexchange.com/questions/44956/good-explanation-of-cascade-on-delete-update-behavior

编辑:在 Postgres 上:

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

http://www.postgresql.org/docs/9.3/static/ddl-constraints.html