在外键列中设置null值？ [英] set null value in a foreign key column?

问题描述

我有这个表格

  CREATE TABLE [dbo]。[CityMaster]（
 [CityID] [int ] NOT NULL，
 [City] [varchar]（100）COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL，
 [BranchId] [varchar]（10）COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__CityM__Branc__74444068] DEFAULT（（0） 
 [ExternalBranchId] [varchar]（max）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
 CONSTRAINT [PK_CityMaster] PRIMARY KEY CLUSTERED 
（
 [City] ASC，
 [BranchId] ASC 
），
 CONSTRAINT [uk_citymaster_cityid_branchid]唯一的非公开
（
 [CityID] ASC，
 [BranchId] ASC 
）
）
  

和另一个表

  CREATE TABLE [dbo]。[CustomerMaster]（
 [ID] [int] NOT NULL，
 [CustomerCode] [varchar]（20）COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL，
 [ CustomerName] [varchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL，
 [CustomerAddress] [varchar]（100）COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL，
 [CustomerPhone] [varchar]（max）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
 [CustomerEmailId] [varchar]（100）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
 [CustomerPriority] [int] NOT NULL CONSTRAINT [DF_CustomerMaster_CustomerPriority] DEFAULT（（0）），
 [CustomerRegisterDate] [datetime] 
 [CustomerIsActive] [bit] NULL CONSTRAINT [DF_CustomerMaster_CustomerIsActive] DEFAULT（（1）），
 [BranchId] [varchar]（10）COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL约束[DF__CustomerM__Branc__67DE6983] DEFAULT 
 [CityId] [int] NULL CONSTRAINT [DF_CustomerMaster_CityId] DEFAULT（（0）），
 CONSTRAINT [PK_CustomerMaster] PRIMARY KEY CLUSTERED 
（
 [CustomerCode] ASC，
 [BranchId] ASC 
）
）ON [PRIMARY] 
 
 ALTER TABLE [dbo]。[CustomerMaster] WITH CHECK ADD CONSTRAINT [fk_cdCityId_CityId] FOREIGN KEY [BranchId]）
 REFERENCES [dbo]。[CityMaster]（[CityID]，[BranchId]）
 
 ALTER TABLE [dbo]。[CustomerMaster] CHECK CONSTRAINT [fk_cdCityId_CityId] $ b $正如你所看到的，在 CityId，BranchId  
 $ c>。我遇到的问题是，如果用户没有进入他的城市（他可以选择不，这个字段不是强制性的，那么 CityId 将是空白的，当我尝试在 CustomerMaster 表中插入此值，我收到此错误说
 
 
 
  INSERT语句冲突与FOREIGN KEY约束fk_cdCityId_CityId冲突发生在数据库TestDatabase，表dbo.CityMaster。
语句已终止。 
 
 
所以，我想知道一个方法来绕过这个，我知道一个唯一的或主键列被引用为外键，它不能为null。但是，时间，当我设置
  on delete set null ？在这种情况下，如果从 CityMaster 中删除​​该行，到 null 在 CustomerMaster （我的意思是它的所有引用）。所以，如果这可能为什么，如何设置这个外键中的值 null 手动？
如果这是不可能的，什么是最好的方法来规避我描述的情况？
解决方案
如果 CityId 设置为 NULL  
 
 
另一方面，如果 CityId ，那么外键约束将不会被检查， c $ c>是0（例如，因为你已经指定了 DEFAULT（（0）） ...），并且<$ c $中没有匹配的行c> CityMaster 为 0，BranchId ，那么这将会导致约束检查失败。
 
 
 
仅当所涉及的列中的全部为非 -   NULL 时，才检查复合键的外键约束。 
I have this table
CREATE TABLE [dbo].[CityMaster](
    [CityID] [int] NOT NULL,
    [City] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [BranchId] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__CityM__Branc__74444068]  DEFAULT ((0)),
    [ExternalBranchId] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_CityMaster] PRIMARY KEY CLUSTERED 
(
    [City] ASC,
    [BranchId] ASC
),
 CONSTRAINT [uk_citymaster_cityid_branchid] UNIQUE NONCLUSTERED 
(
    [CityID] ASC,
    [BranchId] ASC
)
)
and another table
CREATE TABLE [dbo].[CustomerMaster](
    [ID] [int] NOT NULL,
    [CustomerCode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CustomerName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CustomerAddress] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CustomerPhone] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CustomerEmailId] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CustomerPriority] [int] NOT NULL CONSTRAINT [DF_CustomerMaster_CustomerPriority]  DEFAULT ((0)),
    [CustomerRegisterDate] [datetime] NULL,
    [CustomerIsActive] [bit] NULL CONSTRAINT [DF_CustomerMaster_CustomerIsActive]  DEFAULT ((1)),
    [BranchId] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__CustomerM__Branc__67DE6983]  DEFAULT ((0)),
    [CityId] [int] NULL CONSTRAINT [DF_CustomerMaster_CityId]  DEFAULT ((0)),
 CONSTRAINT [PK_CustomerMaster] PRIMARY KEY CLUSTERED 
(
    [CustomerCode] ASC,
    [BranchId] ASC
)
) ON [PRIMARY]

ALTER TABLE [dbo].[CustomerMaster]  WITH CHECK ADD  CONSTRAINT [fk_cdCityId_CityId] FOREIGN KEY([CityId], [BranchId])
REFERENCES [dbo].[CityMaster] ([CityID], [BranchId])

ALTER TABLE [dbo].[CustomerMaster] CHECK CONSTRAINT [fk_cdCityId_CityId]
As you can see, there is a foreign key on CityId, BranchId. The problem I am having is, if a user doesn't enters his city (he can opt not to, this field not mandatory, then the CityId would be blank and when I try to insert this value in the CustomerMaster table, I get this error saying

The INSERT statement conflicted with the FOREIGN KEY constraint "fk_cdCityId_CityId". The conflict occurred in database "TestDatabase", table "dbo.CityMaster".
The statement has been terminated.

So, I wanna know a way to circumvent this. I know if a unique or a primary key column is referenced as a foreign key, it can not be null. But, what about the times, when I've set 
on delete set null? In that case, if that row is deleted from CityMaster it would be set to null in CustomerMaster (I mean all its references). so, if that's possible why and how can I set the value in this foreign key null manually?
And if that's not possible by any means, what's the best way to circumvent the situation I described?
解决方案
If CityId is set to NULL, then the foreign key constraint will not be checked, and all will be good.

On the other hand, if CityId is 0 (say, because you've specified DEFAULT ((0)) on it...), and there's no matching row in CityMaster for 0,BranchId, then this will indeed cause the constraint check to fail.

A foreign key constraint on a composite key is only checked if all of the columns involved are non-NULL.

                        
这篇关于在外键列中设置null值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！