SQL Error when creating a relationship: Unable to create relationship ‘FK_ForeignTable_MasterTable’

September 9, 2009

Today whilst creating relationships within a SQL Server 2005 database I experienced this error:

Unable to create relationship ‘FK_ForeignTable_MasterTable’.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_ForeignTable_MasterTable”. The conflict occurred in database “DatabaseName”, table “dbo.MasterTable”, column ‘ColumnName’.

I have seen this before, but not for some time. It took a bit of thinking to work out what was going on.

This error/warning will occur when there are pre-existing records in the foreign table which do not have a matching (linked) record in the master table.

Fixing this is quite straight foward, and you have 3 options:

1. Fix the links – So if our tables are joined on column A which contains ‘orphaned’ values, change the values in column A so that they link to a master table record

2. Delete the orphaned records, or clear out the table entirely (Careful!)

3.When creating the relationship, set ‘check existing data on creation or re-enabling’ to ‘No’ – This effectivley means ignore the fact that there are orphaned records in the foreign table. Possibly not a good idea. At best, you have records that are probably a waste of resources and will never be seen by anyone. At worst, you risk breaking the data integrity of your application and maybe causing yourself a support call or 4 in months to come!

Obviously only good knowledge of the application itself can help you decide which option is best.

Oh, and if you’ve just found this post via a Google search and it helped you out…Hooray! Now leave me a nice comment! :o)

Unable to create relationship ‘FK_MessagesUsersLookup_aspnet_Users’.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_MessagesUsersLookup_aspnet_Users”. The conflict occurred in database “Activate247”, table “dbo.aspnet_Users”, column ‘UserId’.

– Due to existing data in the foreign table that does not have a matching record in the parent table. Delete the records in the foreign table or set:
‘check existing data on creation or re-enabling’ to ‘No’

Advertisements

5 Responses to “SQL Error when creating a relationship: Unable to create relationship ‘FK_ForeignTable_MasterTable’”

  1. Found via Google; you rock. Why SQL can’t give sane error messages is beyond me. “Hey, Jackass, you have some rows in one of those tables that won’t work with the FK you specified” would be so much more helpful than what looks like a FK conflicting with itself.

    Thanks again.

  2. Zorina said

    Thanks so much for the help! 🙂

  3. eric said

    This is my Nice Comment,

    cheers. Wasted a good few minutes before deciding to ask my mate Google 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: