The INSERT statement conflicted with the FOREIGN KEY constraint even if its null?

Question

Hi,

I have a table that contains a foreign key value(id). This field is able to be null but the problem is that I get the following exception if I set it to null :

The INSERT statement conflicted with the FOREIGN KEY constraint

Why? it is nullable

Edit1 :

USE [biss]
GO

/****** Object:  Table [dbo].[VoucherCodes]    Script Date: 10/16/2011 19:55:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[VoucherCodes](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Code] [nvarchar](50) NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NOT NULL,
    [InactivatedDate] [datetime] NULL,
    [AdCategoryId] [int] NULL,
 CONSTRAINT [PK_VoucherCodes] PRIMARY KEY CLUSTERED 
(
    [Id] 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].[VoucherCodes]  WITH CHECK ADD  
   CONSTRAINT [FK_VoucherCodes_AdCategories] FOREIGN KEY([AdCategoryId])
   REFERENCES [dbo].[AdCategories] ([Id])
   ON UPDATE SET NULL
GO

ALTER TABLE [dbo].[VoucherCodes] CHECK CONSTRAINT [FK_VoucherCodes_AdCategories]
GO

ALTER TABLE [dbo].[VoucherCodes]  WITH CHECK ADD  
   CONSTRAINT [FK_VoucherCodes_VoucherCodes] FOREIGN KEY([Id])
   REFERENCES [dbo].[VoucherCodes] ([Id])
GO

ALTER TABLE [dbo].[VoucherCodes] CHECK CONSTRAINT [FK_VoucherCodes_VoucherCodes]
GO

ALTER TABLE [dbo].[VoucherCodes]  WITH CHECK ADD  
   CONSTRAINT [FK_VoucherCodes_VoucherCodes1] FOREIGN KEY([Id])
   REFERENCES [dbo].[VoucherCodes] ([Id])
GO

ALTER TABLE [dbo].[VoucherCodes] CHECK CONSTRAINT [FK_VoucherCodes_VoucherCodes1]
GO

ALTER TABLE [dbo].[VoucherCodes] ADD  
   CONSTRAINT [DF_VoucherCodes_Inactive]  DEFAULT ((1)) FOR [InactivatedDate]
GO

What is the code that fails? Can you add the full CREATE TABLE too — gbn
FOREIGN KEY (Id) REFERENCES (Id) -- is pointless. Also Id is declared NOT NULL in your SQL DDL, so your assertion "it is nullable" is false. — onedaywhen

gbn gbn · Accepted Answer · 2011-10-16T18:09:03

Your foreign key comes from the two self referencing circular FK constraints.

You have the ID columns as FKs of themselves: of course they will fail because they won't exists at INSERT time.

Run this

ALTER TABLE [dbo].[VoucherCodes]
  DROP CONSTRAINT FK_VoucherCodes_VoucherCodes, FK_VoucherCodes_VoucherCodes1

The INSERT statement conflicted with the FOREIGN KEY constraint even if its null?

1 Answers