Sql-server – Allowing Foreign Key to be Null

database-designsql serverssms

I'm trying to create a relationship between two tables where one column in TABLE A is the foreign key for a column in TABLE B. However, there is one row in TABLE B that is currently null in that column and it's giving me an error. However, from Googling the issue, the general consensus seems to be that null is an acceptable value in a foreign key field. But I'm still unable to create this relationship in SSMS due to the error message. How can I bypass this?

Error: 

Unable to create relationship 'FK_RELATIONSHIP'. 
The ALTER TABLE statement conflicted with the FOREIGN KEY 
constraint "FK_RELATIONSHIP". The conflict occurred in database "DATABASE", 
table "TABLE A", column 'COLUMN 1'.

SQL Code as requested:

USE [DATABASE]
GO
/****** Object:  Table [dbo].[TABLEA]    Script Date: 10/02/2012 17:44:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TABLEA](
[COLUMN1] [nvarchar](4) NOT NULL,
CONSTRAINT [PK_TABLEA] PRIMARY KEY CLUSTERED 
(
[COLUMN1] 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
/****** Object:  Table [dbo].[TABLEB]    Script Date: 10/02/2012 17:44:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TABLEB](
[COLUMN1] [nvarchar](15) NOT NULL,
[COLUMN2] [nvarchar](4) NULL,
 CONSTRAINT [PK_TABLEB] PRIMARY KEY CLUSTERED 
(
[COLUMN1] 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

Thanks

Best Answer

For anyone who stumbles here with the same question. An empty cell is not the same as a NULL cell. Empty cells are '' not NULL!

Related Question