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:
And here is my .sql 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])
);
