INSERT 语句与 FOREIGN KEY 约束冲突，即使它为空？

Question

嗨，

我有一个包含外键值（id）的表。该字段可以为 null，但问题是，如果将其设置为 null，则会出现以下异常：

INSERT 语句与 FOREIGN KEY 约束冲突

为什么？它可以为空

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

Answer 1

您的外键来自两个自引用循环 FK 约束。

您将 ID 列作为其自身的 FK：它们当然会失败，因为它们在 INSERT 时不存在。

运行这个

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