Create foreign key that allows null values in SQL Server

Question

in the client table we have a sex_id field.

I want to create a foreign key that allows null values in the client table and that has a contraint that references the sex table (sex_Id)

Is this possible in sql server and what does the script look like

this is what I have but you can't enter null values in the client table. I want to make this possible.

ALTER TABLE [dbo].[Client] WITH CHECK ADD CONSTRAINT [FK_sex_id] FOREIGN KEY([sex_ID])

REFERENCES [lu].[sex] ([sex_ID])

ON UPDATE CASCADE

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[Client] CHECK CONSTRAINT [FK_sex_id]

GO

Answer 1

You can not use null in a foreign key because null means no match (empty set). Just define an unspecified value.

Answer 2

A nullable column may be used as a foreign key. NULL values will not be checked for the referential integrity check. Example DDL below

CREATE TABLE dbo.sex(
	  sex_ID int NOT NULL
		CONSTRAINT PK_Sex PRIMARY KEY
	, Description varchar(10) NOT NULL
);

CREATE TABLE dbo.Client(
	  ClientID int NOT NULL IDENTITY
	, sex_ID int NULL
);

ALTER TABLE [dbo].[Client] WITH CHECK ADD CONSTRAINT [FK_Client_sex] FOREIGN KEY([sex_ID])
	REFERENCES [sex] ([sex_ID])
	ON UPDATE CASCADE
	ON DELETE CASCADE;
GO

ALTER TABLE [dbo].[Client] CHECK CONSTRAINT [FK_Client_sex];
GO

Answer 3

As Dan says, this is perfectly possible in SQL Server and it is straightforward to do. I suspect that the real problem is that when you think you are inserting NULL in the Client field, you are inserting an empty string, a zero or something similar.