0

I have two tables, Games and Sport_Games. Sport_Games has a foreign key that references Games.

Now when I want to delete an entry from Games I get an error saying:

"FK__Sport_Gam__game___1D7C2B7C". The conflict occurred in database "DatabasesProject", table "dbo.Sport_Games", column 'game_id'. The statement has been terminated.

Im assuming this is because I can't delete something from a table with a constraint without dropping it. I tried adding ON DELETE CASCADE in table Games in hopes that when I delete a game it is also deleted from Sport_Games but that didn't work either.

Here are the tables for reference:

CREATE TABLE Games(

game_id INT IDENTITY PRIMARY KEY,
name VARCHAR(50),
release_date date,
rating INT,
min_age INT,
development_team_email VARCHAR(50) FOREIGN KEY REFERENCES Development_Teams,
release_conference INT FOREIGN KEY REFERENCES Conferences
--ON DELETE CASCADE ---this is what I added 
)

CREATE TABLE Sport_Games(

game_id INT PRIMARY KEY FOREIGN KEY REFERENCES Games,
sport_type VARCHAR(50),

)

I have other tables referencing Games as well, action_games, strategy_games, But they're similar to sport_games

pnuts
  • 54,806
  • 9
  • 74
  • 122
ninesalt
  • 2,968
  • 3
  • 23
  • 49

3 Answers3

1

If you know the GameID of the records you are deleting, simply delete records in the Sport_Games table that have the same GameID first, then delete from Games table.

For the cascade to work, you need to ad that to the FK definition on the Sport_Games table. That way when the Games record is deleted, the sport_Games record will be deleted as well.

datagod
  • 1,021
  • 1
  • 13
  • 21
0

Your ON DELETE CASCADE will work for you if you put it on the correct Foreign Key. But to answer your question...

Since Games.Game_ID has a constraining reference against Sport_Games.Game_ID, you need to either turn the constraint off so it will not prevent you from deleting, but this will leave orphans, or delete the children before you delete the parent. Since you must already know the PK value to delete the parent, you simply:

DELETE FROM Sport_Games WHERE Game_ID = [The id you are using to delete from Games]

Then you delete the parent:

DELETE FROM Games WHERE Game_ID = [Same id used above]
Steve
  • 5,465
  • 1
  • 16
  • 32
  • The thing is that when I'm given an ID of a game to delete, I wont know which genre table (sport_game in this case) its in. – ninesalt Nov 20 '15 at 15:47
  • How many genre tables do you have? If you issue a delete statement from each one, you will eventually find the right one. – datagod Nov 20 '15 at 15:55
  • I have 4 genre tables. I suppose deleting them from the genre table first would be easier, but is there no way I can use on delete cascade to make this easier? I tried putting that in the genre tables but I'm getting an error for some reason. – ninesalt Nov 20 '15 at 16:00
  • I just realized I can't do this because games i just realized that the Game ID is being references by way more than 4 tables – ninesalt Nov 20 '15 at 16:07
  • You got me all confused now. What you are saying is that the Sport_Games holds the Genre, which sounds like it is a parent table, not a child table, and your references/setup above does this the other way around where Games is the parent and Sports_Games is the child. This is probably why you can get this to work and why we are giving you bad advice. Rework the tables and references so they are right and the cascade deletes will work for you. – Steve Nov 20 '15 at 17:03
0

What I'm about to say might not directly answer your question, but it could be beneficial for you moving forward or in future designs.

I've found placing "delete_date" and "delete_id" in tables to be a useful way to work around these FK constraint issues. If your application is only calling existing stored procedures and table functions you could update the queries to include "AND delete_date IS NULL" in their conditions. If your application is writing adhoc queries, it might also be as simple as creating or modifying a base class to inject the condition that omits soft-deleted rows.

Paurian
  • 1,163
  • 8
  • 17
  • 1
    Adding soft deletes as a way to deal with not violating foreign key constraints is not a good approach imho. That is dealing with the symptom instead of the problem. I am NOT saying that soft deletes are a bad thing, I am saying that utilizing soft deletes to avoid RI is not a good reason to use soft deletes. – Sean Lange Nov 20 '15 at 16:03
  • @SeanLange You're right that there are cases where soft deletes are not ideal. We went as far as creating table aliases and views to encapsulate the "AND delete_date IS NULL". Here's a Stack Overflow discussion on it's benefits (all of which we used - 2-pass deletes, data preservation, etc.). http://stackoverflow.com/questions/2549839/are-soft-deletes-a-good-idea – Paurian Nov 20 '15 at 16:21
  • 1
    I was not wanting to have a discussion on the pros and cons of using soft deletes. I said that using them as a way to avoid foreign key constraints is a bad reason to make that decision. – Sean Lange Nov 20 '15 at 16:22