when creating FK constraint get error 'statement conflicted with [an existing] the FK constraint'

Question

when creating FK constraint get error 'statement conflicted with [an existing] the FK constraint'

polkadot

SSChampion

Points: 13664
More actions
May 23, 2017 at 12:27 pm

#337007

been away from development awhile and frustrated by inability to discover underlying issue preventing me from creating FK relationships. The errors refer to enigmaticaly named FKs like 'FK__stg_Lineo__mc_li__787EE5A0' and when i look for them they are not in the table suggested by error:
Error
Msg 547, Level 16, State 0, Line 3 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__stg_Lineo__mc_li__787EE5A0". The conflict occurred in database "MC_POC", table "dbo.stg_LineofBusiness", column 'mc_lineofbusinessid'.
I used this code to see if any FKs exist
SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
I've also gone through the Constraints folders for each table listed in Object Explorer verifying no existing FKs exist, so why am i getting errors that tell me they do?
--Quote me

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's not telling you that there's an existing constraint. It's telling you that the data in the table violates the constraint that you're trying to create, and hence that constraint can't be created. The name given in the error message will be the name of the constraint you're trying to create.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

polkadot SSChampion Points: 13664 More actions · Answer 2

on sql server 2016 I have a bridge table with 3 columns:
applicationID
lineofbusinessID

bridge table exists between tables:
tblApplication (primarykey: applicationID)
tblLineOfBusiness (primary key: lineofbusinessID)

I want two FKs in bridge table referencing Applications and LineOfBusiness primary keys.

Same tables pulled from CRM to power bi desktop have these relationships automatically created on import

In SQL Server I have made all ID fields 'not null'. what are some other reasons i would not be able to create FKs in bridge table to simulate above relationships?

--Quote me

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

There's data in the tables that violates the constraint you're trying to create, so values in the child table that aren't in the parent table.

Can you post the exact statement you're running and the error you're getting?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Joe Torre SSChampion Points: 10248 More actions · Answer 4

If you can't create the constraint, there must be data that violates it as Gail said. (BTW Gail, nice B-5 quote from the Rangers)
select j.*
from mc_mc_lineofbusiness_mc_applicationset j
left join mc_applications a on j.mc_applicationid=a..mc_applicationid;

or
select j.* from mc_mc_lineofbusiness_mc_applicationset j left join mc_lineofbusiness l on j.mc_lineofbusinessid=i.mc_lineofbusinessid;
to find the offending data.

polkadot SSChampion Points: 13664 More actions · Answer 5

GilaMonster:
EXACT STATEMENT
use mc_poc go ALTER TABLE [dbo].[stg_Lineofbusiness_applicationset] ADD FOREIGN KEY ([mc_lineofbusinessid]) REFERENCES [dbo].[stg_LineofBusiness]([mc_lineofbusinessid])

ERROR
Msg 547, Level 16, State 0, Line 3 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__stg_Lineo__mc_li__7B5B524B". The conflict occurred in database "MC_POC", table "dbo.stg_LineofBusiness", column 'mc_lineofbusinessid'.
Joe Torre:
I don't get how left join helps to expose anything here.

I used INTERSECT and found that 474 of 534 applicationIDs in bridge are in Application table.
select mc_applicationid from [stg_Lineofbusiness_applicationset] intersect select mc_applicationid from [stg_Applications];

Are the extra mc_applicationid values in the bridge table finding no match in the Application table cause of problem?

Answer must be no... since all 128 of 128 lineofbusinessid values in bridge are also present in LineOfBusiness table and still I can't create FK

select mc_lineofbusinessid from [stg_Lineofbusiness_applicationset] intersect select mc_lineofbusinessid from [stg_LineofBusiness]

Does every single FK value in bridge table need to be in parent table?

--Quote me

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

polkadot - Tuesday, May 23, 2017 2:27 PM
GilaMonster:
EXACT STATEMENT
use mc_poc go ALTER TABLE [dbo].[stg_Lineofbusiness_applicationset] ADD FOREIGN KEY ([mc_lineofbusinessid]) REFERENCES [dbo].[stg_LineofBusiness]([mc_lineofbusinessid])
ERROR
Msg 547, Level 16, State 0, Line 3 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__stg_Lineo__mc_li__7B5B524B". The conflict occurred in database "MC_POC", table "dbo.stg_LineofBusiness", column 'mc_lineofbusinessid'.
Joe Torre:
I don't get how left join helps to expose anything here.
I used INTERSECT and found that 474 of 534 applicationIDs in bridge are in Application table.
select mc_applicationid from [stg_Lineofbusiness_applicationset] intersect select mc_applicationid from [stg_Applications];
Are the extra mc_applicationid values in the bridge table finding no match in the Application table cause of problem?
Answer must be no... since all 128 of 128 lineofbusinessid values in bridge are also present in LineOfBusiness table and still I can't create FK
select mc_lineofbusinessid from [stg_Lineofbusiness_applicationset] intersect select mc_lineofbusinessid from [stg_LineofBusiness]
Does every single FK value in bridge table need to be in parent table?

The error message is very clear. You have some values in stg_Lineofbusiness_applicationset.mc_lineofbusinessid that do NOT exist in stg_LineofBusiness.mc_lineofbusinessid

You can find them by using a left join as suggested by Joe Torre.

select * from stg_Lineofbusiness_applicationset a left join stg_LineofBusiness lob on lob.mc_lineofbusinessid = a.mc_lineofbusinessid where lob.mc_lineofbusinessid is null

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

polkadot - Tuesday, May 23, 2017 2:27 PM
Does every single FK value in bridge table need to be in parent table?

Yes, that's the meaning of a foreign key. Values in the child table MUST match a value in the parent table. Once the foreign key is created, attempts to insert or update values that don't match the parent will fail. Right now, the data is in violation of the constraint you want to create, therefore the creation of the constraint is failing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

polkadot SSChampion Points: 13664 More actions · Answer 8

Very helpful guys. Thanks everyone.

Joe, I didn't get the query because the where clause wasn't there. You gave me too much credit thinking I'd figure that out!

select *
from stg_Lineofbusiness_applicationset a
left join stg_LineofBusiness lob on lob.mc_lineofbusinessid = a.mc_lineofbusinessid
where lob.mc_lineofbusinessid is null

--Quote me

Joe Torre SSChampion Points: 10248 More actions · Answer 9

Oops, well sorry about that. You would have been able to see the null values had I selected form the left side as well as the right. Oh well.
A foreign key column with a null allowed is a "non-identifying" foreign key. If nulls aren't allowed it's an "identifying" foreign key.

polkadot SSChampion Points: 13664 More actions · Answer 10

OK, all good, thanks everyone for help. Appreciate people are willing to share their talent.

--Quote me