set null value in a foreign key column?

Question

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?

Answer 1

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.

Answer 2

Change in insert query @

[CityId] [int] DEFAULT NULL

This will allow you to store Null value as you declared it as default.