Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

Error 547 raised by ALTER TABLE when creating foreign key constraint

I have two tables as follows:

Table1
- Col1 NOT NULL (PK)
- Col2
- Col3

Table2
- 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...
0
LordSauce
Asked:
LordSauce
1 Solution
 
ibroCommented:
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
LEFT OUTER JOIN Table2 t2
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!
0
 
LordSauceAuthor Commented:
It was due to the data in the referencing tables. Thanks!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now