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
NULL
you are seeing is actually null and not content of nvarchar?NULL
is allowed in a foreign key field: sqlfiddle.com/#!3/ab003. However, if you use the string'NULL'
as the value, I generate the same error message you are getting: sqlfiddle.com/#!3/ab003. If you want to take this a step further, install the SSMS Tools Pack, and use the Generate Insert Scripts wizard to generate SQL for the exact contents of your tables and edit it into your post. You can also use "Generate Change Script" option to include the SQL for your proposed fk_relationship.