title: set null value in a foreign key column?
body: <p>I have this table</p>
  
  <pre><code>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
  )
  )
  </code></pre>
  
  <p>and another table</p>
  
  <pre><code>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]
  </code></pre>
  
  <p>As you can see, there is a foreign key on <code>CityId, BranchId</code>. 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 <code>CityId</code> would be blank and when I try to insert this value in the <code>CustomerMaster</code> table, I get this error saying</p>
  
  <p><strong>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.</strong></p>
  
  <p>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 
  <code>on delete set null</code>? In that case, if that row is deleted from <code>CityMaster</code> it would be set to <code>null</code> in <code>CustomerMaster</code> (I mean all its references). so, if that's possible why and how can I set the value in this foreign key <code>null</code> manually?
  And if that's not possible by any means, what's the best way to circumvent the situation I described?</p>