7

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

12
  • 2
    What is the exact error message you are getting? Are you sure you're creating the FK in the right direction? Oct 2, 2012 at 21:33
  • What is the command you're using to create the FOREIGN KEY. Null-ability of the column in question should be decided by the CREATE TABLE command, not the CONSTRAINT command.
    – Larry Lustig
    Oct 2, 2012 at 21:35
  • 3
    Are you sure it's the fact that a column is NULL, and not that there is a value in the TABLE A column that doesn't match any column in TABLE B? That would report the same error message as well. Can you provide data dumps of your two tables edited into the post or as a pastebin link?
    – mellamokb
    Oct 2, 2012 at 21:39
  • 5
    This will sound silly, but are you sure that NULL you are seeing is actually null and not content of nvarchar?
    – Nikola Markovinović
    Oct 2, 2012 at 22:01
  • 1
    Agree with @Nikola. As you can see here, 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.
    – mellamokb
    Oct 2, 2012 at 22:31

2 Answers 2

5

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!

2
update TABLEA set COLUMN1 = NULL where COLUMN1 = '';
update TABLEB set COLUMN2 = NULL where COLUMN2= '';

If you changing your "NULL" values to none and it works, it might just be that there's an empty string or a hidden character in there.

Try recreating your FK after that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.