Foreign key violation: 7 ERROR

Question

I'm really newbie at this stuff. The thing is... I have article site. People can rate articles there. If no one rated, I can delete article. But if anyone rated article, I keep getting following error:

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

When I check that file, there is followin code:

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');
        }

Could you please help me how to fix it? Thank you in advance

EDIT: this is how it looks 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;
}

Shouldn't there be article_ratings?

You need to delete the correspondent rows in `article_ratings` first as you have a foreign key to `article` in it, and it *has* to reference the `article`. — h2ooooooo, Oct 01 '14 at 13:06
Thank you. Is there way how to do it in file or do I have to do it in database? — Charmed, Oct 01 '14 at 13:10
I don't know your DB class, but I'm sure you can do it. The query is `DELETE FROM table WHERE column = value`. — h2ooooooo, Oct 01 '14 at 13:11
But I will have to do it everytime I will want to delete article with ratings, right? Is there way how to do it automatically when I click on site on "delete article"? — Charmed, Oct 01 '14 at 13:13
Just always run the delete query inside of `handleDeletearticle`. If there's no ratings then nothing will be deleted. — h2ooooooo, Oct 01 '14 at 13:27
Like this? `public function handleDeletearticle($id) { $query = "DELETE FROM article_ratings where id='$id'"; $article = $this->context->createArticles()->get($id); $this->context->createArticles()->where("id", $id)->delete(); $this->flashMessage('Done', 'success'); $this->redirect('Admin:articles'); }` — Charmed, Oct 01 '14 at 13:50
Nope. First of all, you're deleting where `id = $id` in `article_ratings`. Unless `id` is the *article* ID (**not** the rating ID), then this is wrong. It's more likely `article_id = $id`, but I don't know your DB layout. Secondly, you're injecting variables directly into SQL - use prepared statements and bind variables instead. Google will tell you how. Thirdly, you're just setting a string (`$query = '..'`). This does not automatically run the query - it just creates a variable named `$query`. You need to check your DB implementation and `prepare`, `bind` and `execute` (in that order). — h2ooooooo, Oct 01 '14 at 13:52

score 1 · Answer 1 · edited May 23 '17 at 11:46

It really says so in the error message you got, you have a foreign key reference violation. Meaning that you are trying to delete a row which is referenced somewhere in your database, it even tells you where:

is still referenced from table "article_ratings"

You can delete the refering row as well by using ON DELETE CASCADE http://www.mysqltutorial.org/mysql-on-delete-cascade/

There is a question covering this here on SO: MySQL on delete cascade. Test Example

And a great explanation here: https://dba.stackexchange.com/questions/44956/good-explanation-of-cascade-on-delete-update-behavior

Edit: On 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

Thank you. I apologize. I'm really stupid. I don't have access to database. Only to files through FTP. Is there option for that? — Charmed, Oct 01 '14 at 16:52
Well now, you don't have access to any database administration tools? @MikeBrant's solution does not require access to the database. — Thomas Jensen, Oct 01 '14 at 17:26

score 0 · Answer 2 · answered Oct 01 '14 at 14:29

0

As another option to the answer given by @hebron which relies on changing the foreign key cascade delete behavior, you might find it more straightforward and understandable within your code (i.e. not relying on "hidden" database behavior) to delete across a join.

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 = ?

answered Oct 01 '14 at 14:29

Mike Brant

70,514
10
99
103

Thank you. Would this help? `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.hodnoceni=1 LEFT JOIN rating AS rating2 ON rating2.item_id=article.id and rating2.type='article' and rating2.hodnoceni=0 LEFT JOIN \"user\" ON \"user\".id=article.user_id WHERE slug='$slug' GROUP BY article.id, \"user\".id"); return $article; }` – Charmed Oct 01 '14 at 15:59
@Charmed Would that help with what? – Mike Brant Oct 01 '14 at 17:24
I meant... if I can insert your code into the one I posted below you? – Charmed Oct 01 '14 at 18:34
@Charmed I am not sure what the code you posted in comments has to do with making record deletions from the database. If you need to add more code context, you should edit your question to add it there where it is easier to read. – Mike Brant Oct 01 '14 at 18:56
oh. Right. Sorry. I have edited first post. I'm noob. And I just can't seem to find where to edit it/add it. – Charmed Oct 01 '14 at 19:10

Foreign key violation: 7 ERROR

2 Answers2