Javier González
Javier González

Reputation: 89

"The INSERT statement conflicted with the FOREIGN KEY constraint" when insert is NULL

I have this problem: I have this table which has 5 columns: ID, Usuario_IdUsuario, Artista_IdArtista, Disco_IdDisco, Lista_IdLista. The last 4 are foreign keys, and the last 2 allow nulls, because at the time of their creation, the tables they are referencing are empty. So I insert Usuario_IdUsuario and Artsita_IdArtista and I get the following message:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Sigue_Lista". The conflict occurred in database "Tarea2", table "dbo.Lista", column 'IdLista'. The statement has been terminated.

But that table is empty, and not inserting anything there, since it allows nulls. I already checked, it does not have a default value.

Note: This might be considered as "Duplicate" but the answers given in previous questions don't work for me and I can't comment to ask what happens if that doesn't work (default value thing).

The insert code where the problem appears:

string insertQuery2 = "insert into Sigue (Usuario_IdUsuario, Artista_IdArtista) values (@usu, @Artista);"; //if I delete the ; inside the "", then it doesn't show any error messages, but it doesn't isert anything into the table either. 
SqlCommand sig = new SqlCommand(insertQuery2, conn);

sig.Parameters.AddWithValue("@usu", idusu); //UserId taken from user table
sig.Parameters.AddWithValue("@Artista", idar); //ArtistId taken from artist table.

sig.ExecuteNonQuery();

What am I doing wrong?

(I'm working with C# on Visual Studio 2012 and also using SQL Server 2012 with Management Studio)

USE [Tarea2] 
GO

/****** Object:  Table [dbo].[Sigue]    Script Date: 02-11-2014 20:32:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Sigue](
    [IdSigue] [int] IDENTITY(1,1) NOT NULL,
    [Usuario_IdUsuario] [int] NOT NULL,
    [Artista_IdArtista] [int] NOT NULL,
    [Disco_IdDisco] [int] NULL,
    [Lista_IdLista] [int] NULL,
 CONSTRAINT [PK_Sigue] PRIMARY KEY CLUSTERED 
(
    [IdSigue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,     ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Sigue] ADD  CONSTRAINT [DF_Sigue_Disco_IdDisco]  DEFAULT (NULL) FOR     [Disco_IdDisco]
GO

ALTER TABLE [dbo].[Sigue] ADD  CONSTRAINT [DF_Sigue_Lista_IdLista]  DEFAULT (NULL) FOR     [Lista_IdLista]
GO

ALTER TABLE [dbo].[Sigue]  WITH CHECK ADD  CONSTRAINT [FK_Sigue_Artista] FOREIGN     KEY([Artista_IdArtista])
REFERENCES [dbo].[Artista] ([IdArtista])
GO

ALTER TABLE [dbo].[Sigue] CHECK CONSTRAINT [FK_Sigue_Artista]
GO

ALTER TABLE [dbo].[Sigue]  WITH CHECK ADD  CONSTRAINT [FK_Sigue_Disco] FOREIGN     KEY([Disco_IdDisco])
REFERENCES [dbo].[Disco] ([IdDisco])
GO

ALTER TABLE [dbo].[Sigue] CHECK CONSTRAINT [FK_Sigue_Disco]
GO

ALTER TABLE [dbo].[Sigue]  WITH CHECK ADD  CONSTRAINT [FK_Sigue_Lista] FOREIGN     KEY([Lista_IdLista])
REFERENCES [dbo].[Lista] ([IdLista])
GO

ALTER TABLE [dbo].[Sigue] CHECK CONSTRAINT [FK_Sigue_Lista]
GO

ALTER TABLE [dbo].[Sigue]  WITH CHECK ADD  CONSTRAINT [FK_Sigue_UserData] FOREIGN     KEY([Usuario_IdUsuario])
REFERENCES [dbo].[UserData] ([Id])
GO

ALTER TABLE [dbo].[Sigue] CHECK CONSTRAINT [FK_Sigue_UserData]
GO

Upvotes: 2

Views: 4358

Answers (2)

wolfgangwalther
wolfgangwalther

Reputation: 1236

Try explicitly setting the two columns to NULL like this:

INSERT INTO Sigue (Usuario_IdUsuario, Artista_IdArtista, Disco_IdDisco, Lista_IdLista) VALUES (@usu, @Artista, NULL, NULL);

According to the following links, this should work:
set null value in a foreign key column?
Can table columns with a foreign key be null?

Since you mentioned, that the column does NOT have a default value set, you can also try to set the default value to NULL instead.

You can see in this little SQL Fiddle, that inserting NULL into foreign keys is not a problem at all.

After question was edited:
Now, that you provide the info on how you created the foreign keys: You are creating all four foreign keys on the same column IdSigue.

The ALTER TABLE statements should be changed from:

ALTER TABLE [dbo].[Sigue]
  WITH CHECK ADD  CONSTRAINT [FK_Sigue_Artista]
  FOREIGN KEY([IdSigue]) REFERENCES [dbo].[Artista] ([IdArtista])

to:

ALTER TABLE [dbo].[Sigue]
  WITH CHECK ADD  CONSTRAINT [FK_Sigue_Artista]
  FOREIGN KEY([Artista_IdArtista]) REFERENCES [dbo].[Artista] ([IdArtista])

The difference is in the () after FOREIGN KEY.

Do this for the other three foreign key definitions as well.

Last but not least a couple of links on the topic:
How do I create a foreign key in SQL Server?
http://www.sqlinfo.net/sqlserver/sql_server_Create_foreign_key_contraints.php

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 454020

ALTER TABLE [dbo].[Sigue]  
WITH CHECK ADD  CONSTRAINT [FK_Sigue_Lista] FOREIGN KEY([IdSigue])
REFERENCES [dbo].[Lista] ([IdLista])

Should be

ALTER TABLE [dbo].[Sigue]  
WITH CHECK ADD  CONSTRAINT [FK_Sigue_Lista] FOREIGN KEY(Lista_IdLista)
REFERENCES [dbo].[Lista] ([IdLista])

You are validating the wrong column. Currently the behaviour is that it will validate the value in IdSigue appears in [dbo].[Lista]. This isn't the correct semantics.

The same error appears in most of your other FK definitions too.

Upvotes: 3

Related Questions