1

When you have child table and parent table, I know that if I'll use the cascade option it will remove the appropriate child row when I'm removing some row from the parent table.

But what about when you're removing first from the child table some row this will also work? is the appropriate row from the parent table will be removed? Is it possible anyway?

Edit: I have a database for a game which include:

  1. playersTbl (PK = PlayerID)
  2. GamesTbl (PK = GameID)
  3. GameMovesTbl (PK = MoveID,FK = GameID)

Now when the user starts the game he must register to the game (he can be only himself or a part from a group that plays the game)

Now what I want to do is when there is just one player in some game is : When the user want to delete this player from the db, it will remove the record from the playersTbl, the appropriatet game and the game move..

Edit: Right now, the playersTbl and gamesTbl are strangers to each other. so the best solution I see is to create a new table that joins between those tables. Now my DB looks like:

  1. PlayersTbl (PK = PlayerID)
  2. JoinTbl (FK = PlayerID, GameID)
  3. GamesTbl (PK = GameID)
  4. GameMovesTbl (PK = MoveID,FK = GameID)

so if I'm using the cascade option it means that:

  1. PlayersTbl is parent table of JoinTbl
  2. JoinTbl is parent table of GamesTbl
  3. GamesTbl is parent table of GameMovesTbl

But whenever the user deletes some player it removes only from the PlayersTbl and the JoinTbl.. so my question is what is the best relationships between those tables so the delete option will work properly?

Improve this question
5
  • is it possible to post code? Commented Mar 18, 2013 at 18:37
  • 1
    It's possible with triggers. I don't think you can configure referential integrity to cascade deletes up. Commented Mar 18, 2013 at 18:38
  • so just inner join can solve this problem? Commented Mar 18, 2013 at 18:39
  • 1
    This doesn't make sense, if in your business model, you have a 1-1 relationship where the parent table should not exist without the child table, its not really a parent table. The two tables should have their columns combined into one most likely. Can you provide a real example? Commented Mar 18, 2013 at 18:40
  • 2
    What if the parent has other children. Commented Mar 18, 2013 at 18:43

2 Answers 2

Reset to default
3

No, cascading deletes won't remove the parent when the child is deleted... nor should they.

Take for example, a Customer table (the parent) and an Order table (the child). Just because a single Order row is deleted, that doesn't mean that the Customer row that owned the Order should be removed as well. The Customer may have had dozens of other Orders...

If you want deletes to cascade from parent to child, and from child to parent, this would seem to indicate a one-to-one relationship between your tables... at which point you should ask yourself if they really should be two tables or combined into one.

EDIT:

@Elior In your scenario, the Player should be the parent of Game, and Game should be the parent of GameMoves. If you want to remove Player, delete the row, and with cascading deletes enabled, Game rows associated with the Player will be removed, which will then cascade down to remove GameMoves associated with the Game which was removed.

All the cascades are from parent to child... you don't need to remove any parents based on children being removed.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

To elaborate, What if the Customer had other child Orders? Just because one order is removed, you wouldn't want to remove the parent and orphan the other children. Cascading deletes is usually used to prevent orphaned records, such as when you delete the owner, the delete cascades down to children to delete all Orders. So it is only one way, kind of like a fountain where water "cascades" downward, not upward :)
This is the best answer, although the question was really "can this be done", the answer is really "it should NOT be done (and here's why)".
thanks, actually the lets say I have some player named "yoyo" this player can register (just himself) to gameA and after some time, register to gameB as a part from a group. so when the user want to delete a player named yoyo i need to delete all the data that reference to this player. (i know how to delete from table), so in this case it will removed all the data references to yoyo in gameA (the playerData,gameData (gameA), and gameMovesData) and then in gameB it will also remove the row from playersTbl that contains "yoyo" data?
I added more details for my question
1

I think your best bet is to handle this requirement in the application and not in referential integrity using SQL triggers. If your app uses stored procedures it would be fine to put that logic there. Referential integrity is meant to prevent orphaned child records, not to ensure that all parents have child records.

Technically you could create a trigger that deletes the parent if ALL children are deleted, but in the event that there ever IS a case where you want a parent with no children, the triggers will prevent you from doing that.

It's better to have that rule higher up the stack - either in the application or in a stored procedure (if you're using them).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.