外键违规：7错误

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

Answer 2

作为另一种选择通过@hebron依赖于改变外键级联删除行为给出了答案，你可能会发现你的代码中更简单易懂（即不依赖于“隐藏的”数据库行为）在连接中删除。

DELETE articles, article_ratings 
FROM articles 
LEFT JOIN article_ratings 
    ON articles.id = article_ratings.article_id /* or whatever your foreign key name is */ 
WHERE articles.id = ?