Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
2  
What is the exact error message you are getting? Are you sure you're creating the FK in the right direction? –  Martin Smith Oct 2 '12 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 '12 at 21:35
    
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'." If it's in the wrong direction, should it not give an error stating that the field must be unique? –  Tony Oct 2 '12 at 21:36
2  
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 '12 at 21:39
4  
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 '12 at 22:01
show 7 more comments

migrated from stackoverflow.com Oct 3 '12 at 0:59

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 1 down vote accepted

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!

share|improve this answer
add comment
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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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