Conflicting Foreign Key constraints

Oh those elusive little foreign key errors! I ran into one of these a couple months back and decided to reproduce it today to explain how it occurs and what to watch for to avoid.

In my example I have created a simple MOVIES DB with SQL Server 2008 R2. I won’t bother showing the Create Table statements, as that part of the process ran without error. Assume at this point I’ve already successfully created the MOVIES DB with the following 7 tables as follows:

         clip_image001

I need to point out that there are constraints with foreign keys as follows:

· Shelf has a foreign key pointing to DVDCase

· Movie has foreign keys pointing to Shelf and Genre

· Filmography has foreign keys pointing to Actor and Movie

· Review has a foreign key pointing to Movie

Following are my SQL Insert commands to add rows of data to my tables.

USE MOVIES

GO

INSERT DVDCase (CaseID, CaseName)

VALUES (1, ‘Eastwood/Westerns’),

            (2, ‘Comedy/Romance’),

            (3, ‘Mystery’),

            (4, ‘Science Fiction’),

            (5, ‘Drama’)

INSERT Shelf (ShelfID, ShelfName, CaseID)

VALUES (1, ‘Romance’, 2),

            (2, ‘Mystery’, 3),

            (3, ‘Eastwood’, 1),

            (4, ‘Bogart/Bacall’, 3),

            (5, ‘Comedy’, 2),

            (6, ‘Wayne Westerns’, 1)

INSERT Movie (MovieID, Title, ShelfID, GenreID, MovieYear)

VALUES (1, ‘The Good, the Bad, and the Ugly’, 3, 3, ‘1966’),

            (2, ‘Dirty Harry’, 3, 2, ‘1971’),

            (3, ‘The Outlaw Josey Wales’, 3, 3, ‘1976’),

            (4, ‘Sudden Impact’, 3, 2, ‘1983’),

            (5, ‘Bridges of Madison County’, 3, 1, ‘1995’),

            (6, ‘Unforgiven’, 3, 3, ‘1992’),

            (7, ‘Gran Torino’, 3, 2, ‘2008’)

INSERT Genre (GenreID, GenreTitle)

VALUES (1, ‘Romance’),

            (2, ‘Drama’),

            (3, ‘Western’),

            (4, ‘Science Fiction’),

            (5, ‘Mystery’)

INSERT Actor (ActorID, FName, LName)

VALUES (1, ‘Clint’, ‘Eastwood’),

            (2, ‘Burt’, ‘Reynolds’),

            (3, ‘Lee’, ‘Marvin’),

            (4, ‘Sally’, ‘Field’),

            (5, ‘Humphrey’, ‘Bogart’)

INSERT Filmography (ActorID, MovieID)

VALUES (1, 1),

            (1, 2),

            (1, 3),

            (1, 4),

            (1, 5),

            (1, 6),

            (1, 7)

INSERT Review (Comments, MovieID)

VALUES (‘Action packed for Dirty Harry lovers.’, 2),

            (‘Josey Wales is one of his best western heroes!’, 3),

            (‘Where it all started. Spaghetti western fans!’, 1),

            (‘Really enjoyed scenery.’, 3),

            (‘Little rough for youngsters, but good movie.’, 2),

            (‘This is the classic for the man with no name.’, 1)

The above looked good, but when executing against my MOVIES DB I received the following errors:

Msg 547, Level 16, State 0, Line 15  —-  The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Genre”.  —- The conflict occurred in database “MOVIES”, table “dbo.Genre”, column’GenreID’.  —-  The statement has been terminated.

Msg 547, Level 16, State 0, Line 36  —-  The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Movie”.  —- The conflict occurred in database “MOVIES”, table “dbo.Movie”, column ‘MovieID’.  —- The statement has been terminated.

Msg 547, Level 16, State 0, Line 44  —-  The INSERT statement conflicted with the FOREIGN KEY constraint “FK_Movie2” —- The conflict occurred in database “MOVIES”, table “dbo.Movie”, column ‘MovieID’.  —- The statement has been terminated.

I went back and forth attempting to build in my foreign keys within my CREATEs and within ALTER commands, but that had no bearing upon the errors. I looked at Line 15, 36, and 44 to see exactly where I was having the problem. The lines were the INSERT commands for Movie, Filmography, and Review tables. I’ll show these lines again below:

Line 15:    INSERT Movie (MovieID, Title, ShelfID, GenreID, MovieYear)

Line 36:    INSERT Filmography (ActorID, MovieID)

Line 44:    INSERT Review (Comments, MovieID)

The Confusion:

This confused me because I also had the Shelf table which also used a foreign key, but it was not getting the error. I finally stepped back and took stock of what the tables had in common. I was able to see that Filmography and Review both used MovieID as a foreign key to tie it back to Movie table. So this was the common thread. I then inspected Movie table attributes one by one. I was able to isolate the problem down to GenreID by process of elimination. What was wrong with GenreID?

The Problem:

The problem that I had all along was that I was trying to assign a GenreID value into my Movie table, but I had not yet loaded the Genre table! Look above at my SQL script and you will see that I’m trying to use GnereID as a foreign key in line 15 (the INSERT statement for Movie table) before I ever establish this attribute in the Genre table INSERT. What that did was blow up the load of the Movie table, and that in turn (the darn sneaky trickle down attack) corrupted my Filmography and Review tables, because they were in turn trying to load in MovieID from the Movie table which never got loaded.

The Solution:

Once this dawned on me and I moved my INSERT of the Genre table ahead of the Movie table, all was well. The Movie table then loads fine, as do the ones dependent upon it. I hope this may help someone who might run into this same problem. Be careful to establish values for your attributes in your tables before you use them as foreign keys in other tables. Again, simply putting my INSERT for Genre table in front of the INSERT for Movie table fixed all the errors.

LP

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s