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

Question

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

`null` isn't a valid foreign key - what isn't clear about the error message? — Preston Guillot, Nov 02 '14 at 19:18
@PrestonGuillot Where does the error message say anything about `null`? — wolfgangwalther, Nov 02 '14 at 19:57
The title says the value is null. The error message indicates the value violates the foreign key constraint. — Preston Guillot, Nov 02 '14 at 21:32
Well the null in the title is outside the citation. It's not part of the error message, so it is added by OP, assuming that this causes the problem. — wolfgangwalther, Nov 02 '14 at 21:40
The problem is that he's trying to insert a value into a field with a foreign key constraint, and that the value doesn't exist in the table with the relationship. The OP stated that the value that he's trying to insert is `null`. Whether value he's trying to insert is `"A"`, `"George Washington"`, `"foo"`, `4.123441` or `null` is irrelevant, the point is that the value doesn't exist, which is what the error message indicates. — Preston Guillot, Nov 02 '14 at 21:44
@PrestonGuillot it's nothing to do with the null columns. Must be one of the others. You can insert null into a FK column and there is no requirement that a null exists on the other side of the relationship. — Martin Smith, Nov 02 '14 at 21:45
@PrestonGuillot Look at this http://sqlfiddle.com/#!6/4a099/1 and see for yourself: Inserting `NULL` into a foreign key column is not a problem! — wolfgangwalther, Nov 02 '14 at 21:50
Any triggers on the table? Also can you script out the full create table including foreign keys? — Martin Smith, Nov 02 '14 at 21:53
I see you added the CREATE TABLE statement. However we can't see the foreign key definition in this. — wolfgangwalther, Nov 02 '14 at 21:53
You've messed up the FK declarations. They should be using different columns. — Martin Smith, Nov 02 '14 at 22:10

score 3 · Accepted Answer · answered Nov 02 '14 at 22:14

3

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.

answered Nov 02 '14 at 22:14

Martin Smith

438,706
87
741
845

I didn't notice that. I fixed it and now, it doesn't show any error messages, but nothing is inserted into the table either. – Javier González Nov 02 '14 at 23:40
1

@JavierGonzález possibly it is in a transaction that you aren't comitting then. – Martin Smith Nov 02 '14 at 23:58
I don't know what happened, restarted my computer and now my code works :D Thank you thank you thank you :D – Javier González Nov 03 '14 at 01:19

score 0 · Answer 2 · edited May 23 '17 at 12:30

0

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

edited May 23 '17 at 12:30

Community

1
1

answered Nov 02 '14 at 19:54

wolfgangwalther

1,226
7
15

Could you please post your `CREATE TABLE` statement for `Sigue` in your question? Maybe try something like this to create it: http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table – wolfgangwalther Nov 02 '14 at 20:17
Noob Question: How do I post the code here keeping the format of the code? – Javier González Nov 02 '14 at 21:28
Too much code for a comment. Edit your question and put it in there! – wolfgangwalther Nov 02 '14 at 21:31
Why would you ever want null in a foreign key column though? What's the purpose of the foreign key then. – AdamMc331 Nov 02 '14 at 22:04
The purpose of the foreign key is to make sure, that if the column is set to something NOT NULL, this value has to be pointing to a row in another table. It's the same reasoning for a foreign key, without allowing NULL... – wolfgangwalther Nov 02 '14 at 22:06
See http://stackoverflow.com/questions/925203/any-example-of-a-necessary-nullable-foreign-key for examples! – wolfgangwalther Nov 02 '14 at 22:07
Excellent link @wolfgangwalther. I've just never encountered an example like that before. – AdamMc331 Nov 02 '14 at 22:14

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

2 Answers2