C#•2mo ago

Foreign Key Exception on Id when attempting to insert

I am getting the following exception when trying to insert records into my video_genres table:

System.Exception: Microsoft.Data.SqlClient.SqlException The INSERT statement conflicted with the FOREIGN KEY constraint "FK_video_genre_videos". The conflict occurred in database "BlockBusters", table "dbo.videos", column 'id'.
      The statement has been terminated.

System.Exception: Microsoft.Data.SqlClient.SqlException The INSERT statement conflicted with the FOREIGN KEY constraint "FK_video_genre_videos". The conflict occurred in database "BlockBusters", table "dbo.videos", column 'id'.
      The statement has been terminated.

// Attempt to create a link in the join table at [dbo].[video_genres].
foreach (var genre in genres)
{
    if (videoData.Genres != null)
    {
        foreach (var g in videoData.Genres)
        {
            if (g.Genre == genre.Name)
            {
                // Insert the list we created for the videoGenres to the [dbo].[video_genres].
                using (SqlCommand command = new SqlCommand(insertQueryVideoGenres, connection, transaction))
                {
                    command.Parameters.AddWithValue("@VideoId", video.Id);
                    command.Parameters.AddWithValue("@GenreId", genre.Id);
                    command.ExecuteScalar();
                };
            }
        }
    }
}

// Attempt to create a link in the join table at [dbo].[video_genres].
foreach (var genre in genres)
{
    if (videoData.Genres != null)
    {
        foreach (var g in videoData.Genres)
        {
            if (g.Genre == genre.Name)
            {
                // Insert the list we created for the videoGenres to the [dbo].[video_genres].
                using (SqlCommand command = new SqlCommand(insertQueryVideoGenres, connection, transaction))
                {
                    command.Parameters.AddWithValue("@VideoId", video.Id);
                    command.Parameters.AddWithValue("@GenreId", genre.Id);
                    command.ExecuteScalar();
                };
            }
        }
    }
}

And here is my .sql table:

CREATE TABLE [dbo].[video_genres] (
    [id]       INT IDENTITY (1, 1) NOT NULL,
    [video_id] INT NOT NULL,
    [genre_id] INT NOT NULL,
    CONSTRAINT [PK_video_genre] PRIMARY KEY CLUSTERED ([id] ASC),
    CONSTRAINT [FK_video_genre_genres] FOREIGN KEY ([genre_id]) REFERENCES [dbo].[genres] ([id]),
    CONSTRAINT [FK_video_genre_videos] FOREIGN KEY ([video_id]) REFERENCES [dbo].[videos] ([id])
);

CREATE TABLE [dbo].[video_genres] (
    [id]       INT IDENTITY (1, 1) NOT NULL,
    [video_id] INT NOT NULL,
    [genre_id] INT NOT NULL,
    CONSTRAINT [PK_video_genre] PRIMARY KEY CLUSTERED ([id] ASC),
    CONSTRAINT [FK_video_genre_genres] FOREIGN KEY ([genre_id]) REFERENCES [dbo].[genres] ([id]),
    CONSTRAINT [FK_video_genre_videos] FOREIGN KEY ([video_id]) REFERENCES [dbo].[videos] ([id])
);

7 Replies

nitro•2mo ago

More information: It's happening in a function that also handles the creation of the videos, and then attempts to link the genreId and videoId in the video_genres table. Essentially we have data creating record A and using that same data we are trying to create record B (maybe even C, D, E ..)

Pobiega•2mo ago

it sounds like you are trying to add a row in video_genre with a value for video_id which does not exist in videos

nitro•2mo ago

Is that because it has not been made yet for some reason? Oh wait

nitro•2mo ago

The Id seems to be 0..

nitro•2mo ago

And I think it gets set after its record is created in the DB

Pobiega•2mo ago

unless you set it somewhere, it wont be set

nitro•2mo ago

This got solved by the way, thanks for your time.

Gaming

Programming

Foreign Key Exception on Id when attempting to insert