SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Non-Member
    Join Date
    Sep 2006
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL won't let me delete rows

    Hi there,

    I am trying to clean out my database by deleting entries in certain tables. However, I am constantly getting a MySQL error message saying that I can't do so.

    Is there any way I can work around this?

    #1451 - Cannot delete or update a parent row: a foreign key constraint fails (`rayku_db`.`shout`, CONSTRAINT `shout_ibfk_1` FOREIGN KEY (`poster_id`) REFERENCES `user` (`id`))



    Thanks!

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    try using the on delete cascade option, this will remove the orphan rows that would be left in another table with no parent for their foreign key. Are you sure, of course, that you want to delete these rows in the other table as well?

  3. #3
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    or disable the foreign keys first.

    Personally I don't like using cascading deletes because you could accidentally delete rows in other tables that you would normally want to keep.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Kalon View Post
    Personally I don't like using cascading deletes because you could accidentally delete rows in other tables that you would normally want to keep.
    if you normally want to keep them, then you shouldn't have declared the FK with cascading deletes
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's why I don't declare them.

    but every now and then I get a client in the same situation as the OP.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •