Error 547 raised by ALTER TABLE when creating foreign key constraint

Posted on 2001-08-22
Last Modified: 2008-02-26
I have two tables as follows:

- Col1 NOT NULL (PK)
- Col2
- Col3

- Col1 NOT NULL (composite PK)
- Col2 NOT NULL (composite PK)

I am trying to create a foreign key constraint from T1.(C1,C2) to T2.(C1,C2) using the alter table statement, but get the following error:

Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY contraint 'xxx'. The conflict occurred in database 'xxx', table 'xxx', column 'xxx'.

I have another similar case which gives a slightly different error:

Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY contraint 'xxx'. The conflict occurred in database 'xxx', table 'xxx'.

In both cases the name of the constraint given in the error message matches the one being created, and the table is the table of the relationship.

I am using SQL Server 7 with SP3.

I've found some stuff on MSDN about error 547 occurring incorrectly during an update statement, but no mention of 547 with alter table.

Any help gratefully apprecitated...
Question by:LordSauce

Accepted Solution

ibro earned 100 total points
Comment Utility
Your problem probably is that Col2 column in Table1 probaly has either NULL values or values that are not in Table2. So creation of the constraint fails. Check the data in Table1 with statement

select t1.* from Table1 t1
on t1.Col1=t2.Col1 and t1.Col2=t2.Col2
where t2.Col1 is null

This will give you all rows in Table1 which can not be found in Table2 by the constraint Col1, Col2. The trick is in where clause (t2.Col1 is null)!
Hope this helps!

Author Comment

Comment Utility
It was due to the data in the referencing tables. Thanks!

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now