Reputation: 89
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
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
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